Using CTE with a dynamic pivot

Asked Nov 29 '16 at 20:31

Active Nov 29 '16 at 20:31

Viewed 315 times

I'm trying to use This question to perform a dynamic pivot, but I want to use a CTE to get the initial data.

My query looks like this:

DECLARE  @cols  AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

WITH dataSet (coDate, TransactionDate, TotalBalance, TransDate, collected)
AS 
(   *SELECT STATEMENT ) 


SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
    FROM dataSet c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')


 set @query = 'SELECT coDate, ' + @cols + ' from 
        (
            select coDate
                , TotalBalance
                , collected
                , TransDate
            from dataSet
       ) x
        pivot 
        (
             SUM(collected)
            for category in (' + @cols + ')
        ) p '


execute(@query)

And the error SQL gives me is Incorrect syntax near the keyword 'SET'. I did try adding a semicolon and go as well as a comma before the SET statement, but this the first time I've used PIVOT so I'm not sure how CTE interacts with it.

edited May 23 '17 at 12:30

Community

asked Nov 29 '16 at 20:31

Patrick Schomburg

2,494
1
18
46

1

I dont think you can use cte with dinamic pivot. Use cte to populate a temporal table, and use the temporal table in the pivot query. – Juan Carlos Oropeza Nov 29 '16 at 20:37
1

@JuanCarlosOropeza Temporal or temporary? – dfundako Nov 29 '16 at 20:46
@dfundako all depend if you speak spanish or not ;) – Juan Carlos Oropeza Nov 29 '16 at 20:48

Using CTE with a dynamic pivot

0 Answers0