sql - 动态枢轴不显示正确的输出

Question

我有这张表:

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

并试图旋转它以便返回:

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

但是我不确定为什么我下面的脚本会返回不同的东西:

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;

它返回:

    1256|1243
1   Null|Null

不确定我做错了什么:/

Answer 1

你可以稍微简化一下

示例

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

返回

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

生成的 SQL 看起来像这样

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