sql-server - 使用 SQL Server 在 CTE 中自动生成列名

Question

我正在 CTE 内构建一个数据透视查询。我有一张桌子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

这是我用来透视的查询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

这是我得到的结果：

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

结果很好，但现在又增加了一周。

我想开发一个带有枢轴查询的 CTE，它将自动生成不同的周并在此基础上创建一个列。

我做了一些研究，发现可以使用递归 CTE 来做到这一点。我是递归 CTE 的新手，所以请任何人都可以帮助我解决这个问题。

我也尝试过动态数据透视查询，但 CTE 不允许动态查询。

请帮忙。

标签： sql-servertsqlcommon-table-expression

Answer 1

动态枢轴在 CTE 中不起作用

不，但是 CTE 在动态查询中起作用：

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

我可以使用递归 CTE 吗？

不，这不是递归 CTE 的合适用例。