SQL Dynamic Pivot Table

Question

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

The source data is

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

and aiming for

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

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'

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

Any help would be much appreciated

You got an error in your pivot. You need to change `(JobID, SalesForMonth)` to `sum(SalesForMonth)`. Here is a [demo](http://data.stackexchange.com/stackoverflow/query/618823). Then if you want a total by JobId you can use `sum(SalesForMonth) over...()` - here is another demo - http://data.stackexchange.com/stackoverflow/query/618826 — Taryn, Jan 23 '17 at 13:26

John Cappelletti · Answer 1 · 2017-01-23T15:02:36.300

0

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

Returns

EDIT - Dynamically Create View

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

edited Jan 23 '17 at 15:02

answered Jan 23 '17 at 13:27

John Cappelletti

79,615
7
44
66

Care to comment on the down vote? – John Cappelletti Jan 23 '17 at 13:32
Hi John, I'm quite sure, that the down-vote is bound to the fact, that this question is answered several times before and people try to keep SO tidy... I placed an answer only because I thought there was an obvious error, but I was a blind olm :-) Anyway, I'll delete mine as it is later than yours... – Shnugo Jan 23 '17 at 13:39
Thanks so much, exactly what I needed. Still unsure as to the purpose of the XML path bit though? – PaulStephen Jan 23 '17 at 13:57
@PaulStephen The XML portion will dynamically build a string of unique columns names/values to pivot. To see what the dynamic SQL looks like, just add Select {at}SQL after the Exec({at}SQL) – John Cappelletti Jan 23 '17 at 14:00
Ah OK I think I get that. Actually very handy for another thing I need to work on. Thanks again :) – PaulStephen Jan 23 '17 at 14:04
@PaulStephen happy it helped. – John Cappelletti Jan 23 '17 at 14:06
Knew there would be another stumbling block. I forgot that I can't declare in a view. I know there will be solutions on here somewhere so I don't want to extend this thread :) – PaulStephen Jan 23 '17 at 14:28
@PaulStephen No dynamic SQL in a view – John Cappelletti Jan 23 '17 at 14:36
@PaulStephen See Edit to dynamically drop and re-create view – John Cappelletti Jan 23 '17 at 15:02
Thanks, I will try to get my head around that. Could it be done in a stored procedure also? – PaulStephen Jan 23 '17 at 16:33
@PaulStephen Absolutely – John Cappelletti Jan 23 '17 at 16:36

SQL Dynamic Pivot Table

1 Answers1

Linked