All Columns in 1 Column

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

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

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

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

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

Union ( will help to unite into single columns)

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

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