CODEWITHSUNDEEP
sqlsql-servert-sqldynamicpivot
VS1SQL
VS1SQL

Reputation: 155

Dynamic Pivot Not displaying right output

I have this table:

Year Month| User| Player  | Manager
1996-06   | 1256|  2      |   1
1997-07   | 1243|  5      |   2

and was trying to pivot it so it returns:

       | 1997-06|1996-07
User   | 1256   | 1243
Player | 2      |   5
Manager| 1      |   2

however I'm not to sure why my script below is returning something different:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME([User]) 
  FROM (SELECT p.[User] FROM dbo.practise AS p
  GROUP BY p.[User]) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT [year month], [User]
   FROM dbo.practise AS p

) AS j
PIVOT
(
  SUM([User]) FOR [year month] IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

It returns:

    1256|1243
1   Null|Null

Not sure what I'm doing wrong :/

Upvotes: 3

Views: 92

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Your could simplify this a bit

Example

Declare @SQL varchar(max) = '
Select *
 From  (
        Select [Year Month]
              ,B.*
         From practise
         Cross Apply (values (''User''   ,cast([User]  as varchar(max)))
                            ,(''Player'' ,cast(Player  as varchar(max)))
                            ,(''Manager'',cast(Manager as varchar(max)))
                     ) B (Item,Value)

       ) A
 Pivot (max([Value]) For [year month] in (' + Stuff((Select Distinct ','+QuoteName([year month]) 
                                                      From practise
                                                      Order By 1 
                                                      For XML Path('')),1,1,'')  + ') ) p
 Order by 1 Desc'
Exec(@SQL);
--Print @SQL

Returns

Item    1996-06   1997-07
User    1256      1243
Player  2         5
Manager 1         2

The Generated SQL Looks Like This

Select *
 From  (
        Select [Year Month]
              ,B.*
         From practise
         Cross Apply (values ('User'   ,cast([User]  as varchar(max)))
                            ,('Player' ,cast(Player  as varchar(max)))
                            ,('Manager',cast(Manager as varchar(max)))
                     ) B (Item,Value)

       ) A
 Pivot (max([Value]) For [year month] in ([1996-06],[1997-07]) ) p
 Order by 1 Desc

Upvotes: 1

Related Questions