Column Cell to header - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Column Cell to header

Author

Topic

n3xus
Starting Member

6 Posts

Posted - 2011-04-08 : 01:39:54

This is my table

EMP_ID	[DATE]	Attendance
106205	2011-02-01	A
106206	2011-02-01	S
106207	2011-02-01	A
106205	2011-02-02	A
106206	2011-02-02	A
106207	2011-02-02	A

I'm trying query it like this. Is it possible to do it in SQL Server?


	2011-02-01	2011-02-01
106205	A		A
106206	S		A
106207	A		A

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-04-08 : 02:11:00

If you want the columns to be added dynamically please search on this site for dynamic pivot/crosstab article. To use static column list:

-- using case
select EMP_ID,
max(case when [DATE] = '2011-02-01' then Attendance else null end) [2011-02-01],
max(case when [DATE] = '2011-02-02' then Attendance else null end) [2011-02-02]
from @t
group
by EMP_ID

select EMP_ID,
[2011-02-01],
[2011-02-02]
from @t p
pivot (max(Attendance) for [DATE] IN ([2011-02-01],[2011-02-02])) as pvt

Nathan Skerl

McDebil
Starting Member

23 Posts

Posted - 2011-04-08 : 03:10:56

Change the table name 'StuffTest' to your table name.

DECLARE @sqlString AS NVARCHAR(MAX)

SET @sqlString = 'SELECT EMP_ID,' + STUFF((SELECT DISTINCT (',[' + [DATE] + ']') FROM StuffTest FOR XML PATH('')),1,1, '') +
'FROM StuffTest p' + CHAR(13) +
'PIVOT (MAX(Attendance) FOR [DATE] IN (' + STUFF((SELECT DISTINCT (',[' + [DATE] + ']') FROM StuffTest FOR XML PATH('')),1,1, '') +')) as pvt'

EXECUTE (@sqlString)

McDebil

n3xus
Starting Member

6 Posts

Posted - 2011-04-12 : 03:05:32

thank you so much guys. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources