使用数据透视表将 SQL 查询转换为表

Question

>我能知道如何修改下面的SQL查询，以便使用数据透视表将结果转换为预期结果，其中((在"月年=开始日期"和"结束日期"中由用户选择？

SQL 查询： SQL 查询结果 (图片(

预期结果：表格(图像(

SELECT * FROM
(
SELECT WR.Work_Type,
LEFT(DATENAME(MONTH,WR.Request_Date),3)+' '+STR(YEAR(WR.Request_Date),4) AS MonthYear,
CONVERT(INT,STR(YEAR(WR.Request_Date),4)+REPLACE(STR(MONTH(WR.Request_Date),2),' ','0') ) AS MonthYearOrder,
COUNT(ISNULL(WR.Request_ID,0)) AS Total 
FROM tblWork_Request WR
INNER JOIN vWorkTypeByPropertyLevel WT ON WR.CoID=WT.CoID AND WR.Work_Type=WT.Work_Type AND WT.IsWorkRequest=1 
LEFT JOIN [UBERIQ_1.5_Property].[dbo].[tblProperty] P ON WR.Property_ID=P.Property_ID                          
WHERE WR.CoID='59' and WR.Property_ID='MCST001' 
GROUP BY WR.Work_Type,LEFT(DATENAME(MONTH,WR.Request_Date),3)+' '+STR(YEAR(WR.Request_Date),4),
CONVERT(INT,STR(YEAR(WR.Request_Date),4)+REPLACE(STR(MONTH(WR.Request_Date),2),' ','0') ) 
) t
pivot (
SUM(Total)
FOR MonthYear IN ()
) as pivot_table

结果：-

Work_Type          | MonthYear | MonthYearOrder | Total
----------------------------------------------------------
A.1 Problem Code 1 | Jun 2020  | 202006         | 3
AHU faulty         | Nov 2020  | 202011         | 6
AirCondition       | May 2020  | 202005         | 1
Exterior           | Jun 2020  | 202006         | 2

预期成果 ：-

Work Type           | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020 | May 2020 | Jun 2020 | Jun 2020 | Jul 2020 | Aug 2020 | Sep 2020 | Oct 2020 | Nov 2020 | Dec 2020 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
A.1 Problem Code 1  | 0        | 0        | 0        | 0        | 0        | 3        | 0        | 0        | 0        | 0        | 0        | 0        | 0        |  
AHU Faulty          | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 6        | 0    |
AirCondition        | 0        | 0        | 0        | 0        | 1        | 0        | 0        | 0        | 0        | 0        | 0        | 0        | 0   |
Exterior            | 0        | 0        | 0        | 0        | 0        | 2        | 0        | 0        | 0        | 0        | 0        | 0        | 0   |

请指教。谢谢。

Answer 1

--Variables for dynamic columns and query
DECLARE @columns NVARCHAR(MAX) = ''
DECLARE @ifnullcolumns     NVARCHAR(MAX) = ''
DECLARE @query     NVARCHAR(MAX)
;WITH months(YearMonth) AS
(
SELECT 202001
UNION ALL
SELECT YearMonth + 1
FROM months
WHERE YearMonth < 202012
)
SELECT 
@columns += QUOTENAME(YearMonth) + ',',
@ifnullcolumns += 'ISNULL(' + QUOTENAME(YearMonth) + ', 0) AS ' + QUOTENAME(YearMonth)+','
FROM 
months
-- remove last , from the column list
SET @columns = LEFT(@columns, LEN(@columns) - 1)
SET @ifnullcolumns = LEFT(@ifnullcolumns, LEN(@ifnullcolumns) - 1)
-- Building dynamic query with pivot
SET @query ='
SELECT Work_Type, '+ @ifnullcolumns +' FROM (
select Work_Type, MonthYerOrder, Total
from Sample) t
PIVOT(
SUM(Total)
FOR MonthYerOrder IN ('+ @columns +')
) AS pivot_table;'
EXECUTE sp_executesql @query

此查询的结果：http://sqlfiddle.com/#!18/96333/63