使用透视表将sql查询转换为表

w80xi6nr 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(513)

我可以知道如何修改下面的sql查询，以便使用pivot table where（）将结果转换为预期结果吗？对于monthyear=start date和end date selected by user？
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   |

请告知。谢谢您。

sql sql-server pivot pivot-table

来源：https://stackoverflow.com/questions/62424297/convert-sql-query-to-table-using-pivot-table

1条答案

按热度按时间

6yoyoihd1#

--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

赞(0）回复(0）举报 2021-07-26

我来回答

使用透视表将sql查询转换为表

1条答案

相关问题

热门标签

最新问答