SQL 动态数据透视表 [英] SQL Dynamic Pivot Table

问题描述

我在制作动态数据透视表时遇到错误

I'm struggling with an error while making a dynamic pivot table

源数据为

JobID | SalesForMonth | YearMonth
7734  |   400         | 2016-12
7734  |   350         | 2017-01
8540  |   444         | 2016-12
8540  |   300         | 2017-01

并瞄准

JobID | 2016-12 | 2017-01
7734  |   400   |   350
8540  |   444   |   300

并且我尝试使用我在此处找到的查询来创建列标题.但必须承认我并不真正理解For XML"这一行，并在第 6 行出现语法错误

and I've tried to use a query I found on here to create the column headers. But must admit I don't really understand the 'For XML' line and getting a syntax error there on line 6

DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YearMonth) 
                FROM v_JobSalesByMonth
        FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SELECT @query = 
'SELECT * FROM
(SELECT JobID, YearMonth, SalesForMonth
FROM v_JobSalesByMonth) X
PIVOT 
(
(JobID, SalesForMonth)
for [YearMonth] in (' + @cols + ')
) P'

我还想为 jobID 列保留总销售额"

I'd also like to stick in a 'total sales' for the jobID column

任何帮助将不胜感激

推荐答案

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(YearMonth) From v_JobSalesByMonth  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [JobID],[TotalSales],' + @SQL + '
From (
        Select JobID
              ,TotalSales = sum(SalesForMonth) over (Partition By JobID)
              ,YearMonth
              ,SalesForMonth
         From v_JobSalesByMonth A
     ) A
 Pivot (sum(SalesForMonth) For [YearMonth] in (' + @SQL + ') ) p'
Exec(@SQL);

退货

编辑 - 动态创建视图

由于您不能在视图中使用动态 SQL，您可以安排作业(每天或每月)删除并重新创建视图.

Since you can't have dynamic SQL in a view, you could have job scheduled (daily or monthly) to drop and re-create the view.

if object_id('vw_SalesByMonth','v') is not null
drop view vw_SalesByMonth;


Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(YearMonth) From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Create View vw_SalesByMonth
AS

Select [JobID],[TotalSales],' + @SQL + '
From (
        Select JobID
              ,TotalSales = sum(SalesForMonth) over (Partition By JobID)
              ,YearMonth
              ,SalesForMonth
         From YourTable A
     ) A
 Pivot (sum(SalesForMonth) For [YearMonth] in (' + @SQL + ') ) p'
Exec(@SQL);

Select * from vw_SalesByMonth

这篇关于SQL 动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！