How to pivot table rows into columns with dynamic name

Question

I'm having a very tough time trying to figure out how to do

CID  sUSER  VALUE
------------------
001  235      10
001  188      20
001   04      5
002  235      11
002  188      12
002   04      13

I would like it displayed as follows

CID  04   188  235
-------------------
001  5     20   10
002  13    12   11

Can someone please show me the Sql code please ?

This is hard. You need to use dynamic pivot. See here. http://stackoverflow.com/questions/15714265/i-need-to-know-how-to-create-a-crosstab-query — PeterRing, Jan 23 '14 at 10:35

score 0 · Answer 1 · answered Jan 23 '14 at 10:41

Lets Assume you have filtered data in #tmpCID; now try below script

Declare @sUser As Varchar(Max)
SELECT @sUser= Case isNull(@sUser,'')
        When '' then '['+ Rtrim(sUser) + ']'
        Else  COALESCE(@sUser + ',[', ',') +  Rtrim(sUser)+ ']'
        End
         FROM (SELECT DISTINCT sUser FROM #tmpCID) U


Declare @Query As Varchar(Max)
Set @Query='Select CID, ' + @sUser + ' From #tmpCID
    Pivot (AVG(Value)
    For sUser IN     ('+ @sUser + ')) As P'

EXEC  (@Query)

immayankmodi · Answer 2 · 2014-01-23T11:29:24.590

Try this:

CREATE PROCEDURE YourProcedureName
as
Begin 

DECLARE @sUSER AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @sUSER = STUFF((SELECT distinct ',' + QUOTENAME(sUSER) FROM testPIVOT
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');


SELECT @query = 'WITH PivotData AS
(
   SELECT CID, sUSER, VALUE
   FROM dbo.YourTableName
)

SELECT CID, '+ @sUSER +'
FROM PivotData
PIVOT(max(VALUE) FOR sUSER IN('+ @sUSER +')) AS P;';

execute(@query);

End

Hope it could help you!

Thanks.

How to pivot table rows into columns with dynamic name

2 Answers2