Friday, May 10, 2013

[SQL Server Data Warehousing] sqlserver piovting


Hi Kiran,


Try like  this,



--CREATE TABLE ##TMP(EMPID INT,Question1 VARCHAR(10),Question2 VARCHAR(10) ,Question3 VARCHAR(10) ,LogDate VARCHAR(10) )
--INSERT ##TMP SELECT 675440,'bbb','bbb','bbb','2012-11-07'
--INSERT ##TMP SELECT 675440,'aaa','aaa','aaa','2012-11-08'
--INSERT ##TMP SELECT 683604,'ddd','ddd','ddd','2012-11-07'
--INSERT ##TMP SELECT 683604,'ccc','ccc','ccc','2012-11-08'
--INSERT ##TMP SELECT 683604,'tttt','tttt','tttt','2012-11-09'
DECLARE @Columns NVARCHAR(MAX)
,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
(SELECT ', ' +'['+LogDate+']' FROM
(SELECT DISTINCT LogDate FROM ##TMP ) AS T FOR XML PATH('')),1,2,'')

SET @query = N'
SELECT EMPID,' + @Columns + '
FROM
(
SELECT LogDate,EMPID,Question1 FROM ##TMP
) i
PIVOT
(
MAX(Question1) FOR LogDate IN ('
+ @Columns
+ ')
) j';
EXEC sp_executesql @query;
--



sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.




.

social.technet.microsoft.com/Forums

No comments:

Post a Comment

Search This Blog