All Columns in 1 Column

Posted by Ravimakhija88 under Sql Server on 1/19/2013 | Points: 10 | Views : 726 | Status : [Member] | Replies : 6
Hi All..

actually my table structure is like that..

name | date_col_depta | date_col_deptb| date_col_deptc
----------------------------------------------------
dept a | xx | |
dept b | | xx |
dept c | | | xx

for each dept there will be one date

& i want output like that (without using case statement)

name | dept_date
----------------------------------------------------
dept a | xx
dept b | xx
dept c | xx

Thanks

**Pls i want output without using case statement .. as in actually there are more than 3 cols




Responses

Posted by: Kundnani.Rt on: 1/20/2013 [Member] Starter | Points: 25

Up
0
Down
I think you have to do Logical Manipulation while rendering the data

Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 1/20/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use COALESCE statement!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 1/21/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Check this code


Declare @temp table(
name varchar(10),
depta varchar(3),
deptb varchar(3),
deptc varchar(3)
)

insert into @temp
select 'depta','xx',null,null
union
select 'deptb',null,'xx',null
union
select 'deptc',null,null,'xx'

select * from @temp



Select name,orders as dept_date
from
(
select name,depta,deptb,deptc from @temp
) as p
UNPIVOT
(
orders for Employee in (depta,deptb,deptc)
) as unpvt
order by name




Please mark this answer, if it resolves your problem.

Sourabh07

Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prashikudupi on: 1/23/2013 [Member] Starter | Points: 25

Up
0
Down
CREATE TABLE DEPT(NM VARCHAR(10),DEPTA VARCHAR(10),DEPTB VARCHAR(10),DEPTC VARCHAR(10))

INSERT INTO DEPT VALUES ('DEPT A','XX','',''),('DEPT B','','XX',''),('DEPT C','','','XX')

SELECT * FROM DEPT

NM DEPTA DEPTB DEPTC
---------- ---------- ---------- ----------
DEPT A XX
DEPT B XX
DEPT C XX

SELECT NM,DEPT_DT
FROM(SELECT NM,DEPTA,DEPTB,DEPTC FROM DEPT)A
UNPIVOT (DEPT_DT FOR DEPT IN (DEPTA,DEPTB,DEPTC ))AS UNPVT

--Since we have '' in columns we are getting the output as shown below
NM DEPT_DT
---------- ----------
DEPT A XX
DEPT A
DEPT A
DEPT B
DEPT B XX
DEPT B
DEPT C
DEPT C
DEPT C XX

--Final query --remove blank rows
SELECT NM,DEPT_DT
FROM(SELECT NM,DEPTA,DEPTB,DEPTC FROM DEPT)A
UNPIVOT (DEPT_DT FOR DEPT IN (DEPTA,DEPTB,DEPTC))AS UNPVT
WHERE DEPT_DT<>''

NM DEPT_DT
---------- ----------
DEPT A XX
DEPT B XX
DEPT C XX

I have explained about PIVOT and UNPIVOT with simple examples here

http://sqlbay.blogspot.in/2013/01/how-to-pivot-and-unpivot-tables.html





Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 2/20/2013 [Member] Starter | Points: 25

Up
0
Down
Union ( will help to unite into single columns)

Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/21/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Did you try COALESCE ?

Check It Out!
CREATE TABLE Table1 

(
name VARCHAR(10),
date_col_depta VARCHAR(10),
date_col_deptb VARCHAR(10),
date_col_deptc VARCHAR(10)
)
GO

INSERT Table1 VALUES('dept a','xx',NULL,NULL)
INSERT Table1 VALUES('dept b',NULL,'xx',NULL)
INSERT Table1 VALUES('dept c',NULL,NULL,'xx')
GO

SELECT name, COALESCE(date_col_depta,date_col_deptb,date_col_deptc) dept_date
FROM Table1
GO


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ravimakhija88, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response