将静态透视更改为动态透视 SQL

Question

我已经完成了静态透视，这意味着我列出了枢轴列名称。这是我的代码。

SELECT Date, 
ISNULL(XSP, 'X')as XSP,
ISNULL(BSR, 'X')as BSR,
ISNULL(BPT, 'X')as BPT,
ISNULL(XPW, 'X')as XPW,
ISNULL(IPH, 'X')as IPH,
ISNULL(XTH, 'X')as XTH,
ISNULL(TGG, 'X')as TGG,
ISNULL(XKG, 'X')as XKG,
ISNULL(XKM, 'X')as XKM,
ISNULL(XLG, 'X')as XLG,
ISNULL(KBR, 'X')as KBR,
ISNULL(KUA, 'X')as KUA,
ISNULL(LGK, 'X')as LGK,
ISNULL(MKZ, 'X')as MKZ,
ISNULL(PEN, 'X')as PEN,
ISNULL(XGM, 'X')as XGM,
ISNULL(JHB, 'X')as JHB,
ISNULL(XBN, 'X')as XBN,
ISNULL(STW, 'X')as STW,
ISNULL(TIN, 'X')as TIN,
ISNULL(MUA, 'X')as MUA
FROM
(SELECT DISTINCT userid, status , DATEPART(dd, [date]) as Date
FROM edrsDB..tbl_status
WHERE DATEPART(MM, [date]) = 3
And DATEPART(YYYY, [date]) = 2017
Union 
SELECT DISTINCT userid, status , DATEPART(DD,[date]) as Date
FROM edrsDB..tbl_public_holiday
WHERE DATEPART(MM, [date]) = 3
And DATEPART(YYYY, [date]) = 2017
Union
SELECT DISTINCT userid, status , DATEPART(DD,[Date]) as Date 
FROM edrsDB..tbl_station_weekend
WHERE DATEPART(MM, [date]) = 3
And DATEPART(YYYY, [date]) = 2017
And date IS NOT NULL) AS monthlyRpt
PIVOT (MAX(status)
FOR userid IN (XSP, BSR, BPT, XPW, IPH, XTH, TGG, XKG, XKM, XLG, KBR, KUA, LGK,MKZ, PEN, XGM, JHB, XBN, STW, TIN, MUA)) AS pivottable

执行所有这些代码后，它将使输出如下所示，该列包括日期 和所有代码名称

这是上面查询的输出图像

那么我如何将代码更改为动态透视......任何想法？

Answer 1

假定您有一个包含用户标识的表。您的查询将如下所示：

   DECLARE @User AS TABLE (UserId varchar(50))
   INSERT INTO @User VALUES ('XSP'), ('BSR'), ('BPT')
   DECLARE @HeaderText nvarchar(max) 
   DECLARE @ColumnPiVot nvarchar(max)
   SELECT @ColumnPiVot = Stuff((SELECT concat(', ',td.UserId) FROM (select DISTINCT tt.UserId  
                                                         FROM @User tt ) td FOR XML PATH (''))
                         ,1,1,''),
         @HeaderText = CONCAT( N'Date, ' ,  Stuff((SELECT concat(', ISNULL(',td.UserId,',0) AS', ' ',td.UserId ) 
                                            FROM (select DISTINCT tt.UserId FROM @User tt ) td FOR XML PATH (''))
                                      ,1,1,''))
   DECLARE @query nvarchar(max) = CONCAT(N'SELECT ' , @HeaderText,
   ' FROM
      (
         SELECT DISTINCT userid, status , DATEPART(dd, [date]) as Date
         FROM edrsDB..tbl_status
         WHERE   DATEPART(MM, [date]) = 3
                And DATEPART(YYYY, [date]) = 2017
         Union 
         SELECT DISTINCT userid, status , DATEPART(DD,[date]) as Date
         FROM edrsDB..tbl_public_holiday
         WHERE   DATEPART(MM, [date]) = 3
                And DATEPART(YYYY, [date]) = 2017
         Union
         SELECT DISTINCT userid, status , DATEPART(DD,[Date]) as Date 
         FROM edrsDB..tbl_station_weekend
         WHERE   DATEPART(MM, [date]) = 3
                And DATEPART(YYYY, [date]) = 2017
                And date IS NOT NULL
      ) AS monthlyRpt
   PIVOT 
      (MAX(status) FOR userid IN, (',@ColumnPiVot,')) AS pivottable')
   PRINT @query
   exec sp_executesql  @query