Automatically generate columns name in CTE using SQL Server

Question

I am building a pivot query inside a CTE. I have a table Table_1:

Store      Week     xCount
-------    ----     ------
101        1        138
105        1        37
109        1        59
101        2        282
109        2        97
105        3        60
109        3        87

This is the query I used to pivot Table_1:

with CTE as 
(
    select 
        *
    from 
        (select 
             store, week, xCount
         from 
             table_1) src
    pivot
        (sum(xcount)
             for week in ([1], [2], [3])
    ) piv;
)
Select * 
From CTE

And this is the result I got:

| STORE |   1 |   2 |   3 |
+-------+-----+-----+-----+
|   101 | 138 | 282 | null|
|   105 |  37 | null|  60 |
|   109 |  59 |  97 |  87 |

The result is fine, but now there is one more WEEK added.

I want to develop a CTE with pivot query that will automatically generate distinct weeks and create a column on that basis.

I did some research and found a recursive CTE can be used to do this. I am new to recursive CTE, so please anyone can help me to solve this issue.

I also tried dynamic pivot query but CTE does not allow dynamic query.

Please help.

Seems yo me like you are looking for [dynamic pivot.](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Zohar Peled, May 02 '19 at 15:29
Yes a dynamic pivot works in a CTE, as long as the entire query including the CTE is dynamic, which is the solution you need. — Tab Alleman, May 02 '19 at 15:39
No, it won't work inside a cte (unless the cte itself is a part of the dynamic query). However, the only way to return a dynamic number of columns is to use dynamic SQL, so perhaps you should consider giving up the cte... — Zohar Peled, May 02 '19 at 15:40
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ — Aaron Bertrand, May 02 '19 at 16:17
@SqlProgrammer , if any of the below solution worked then consider [Accepting Answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) or add more info/comment to the question if anything is missing — CuriousKid, May 03 '19 at 17:54

score 2 · Accepted Answer · answered May 02 '19 at 15:46

2

dynamic pivot doesn't work inside CTE

No, but a CTE works inside a dynamic query:

{assuming you have declared the variables used below}
SELECT @Cols = {query to get the column names in a comma-separated string}

SET @sql='
with CTE as 
(
    select 
        *
    from 
        (select 
             store, week, xCount
         from 
             table_1) src
    pivot
        (sum(xcount)
             for week in ('+@Cols+')
    ) piv;
)
Select * 
From CTE
'

EXEC (@sql)

Can i use recursive CTE?

No this isn't an appropriate use-case for a recursive CTE.

answered May 02 '19 at 15:46

Tab Alleman

31,483
7
36
52

But i want to use this CTE inside other code and i don't want to put every code inside dynamic query – Sql Programmer May 02 '19 at 16:33
@SqlProgrammer , Then can you add full scenario in your question? – CuriousKid May 02 '19 at 16:40
You can only use a CTE with one query. What other code are you thinking you can use it in? Maybe you need a temporary table instead of a CTE. – Tab Alleman May 02 '19 at 18:06

score 1 · Answer 2 · answered May 02 '19 at 16:01

/* Variable to hold unique Week to be used in PIVOT clause */
DECLARE @Weeks  NVARCHAR(MAX) = N''
/* Extract unique Week names with pivot formattings */
SELECT @Weeks  = @Weeks  + ', [' + COALESCE(week, '') + ']'
FROM (SELECT DISTINCT week FROM table_1) DT
/* Remove first comma and space */
SELECT @Weeks = LTRIM(STUFF(@Weeks , 1, 1, ''))


/* Variable to hold t-sql query */
DECLARE @CTEStatement NVARCHAR(MAX) = N''
/* Generate dynamic PIVOT query here */
SET @CTEStatement=N'
;WITH CTE as 
( SELECT *
    FROM 
        (SELECT
             store
            ,week
            ,xCount
         FROM 
             table_1) SRC
    PIVOT
        (SUM(xcount)
             FOR week in ('+ @Weeks +')
    ) PIV;
)
SELECT * 
FROM CTE
'

EXEC (@CTEStatement)

Automatically generate columns name in CTE using SQL Server

2 Answers2