SQL Server动态Pivot问题：拼接加分隔符及透视结果引用

问题1：CONCAT拼接插入分隔符 #

使用SQL Server原生支持的CONCAT_WS函数即可实现带分隔符的字段拼接，函数第一个参数为自定义分隔符，后续参数为待拼接的字段列表。
你只需要将动态SQL中concat('+@cols+' )as NewCol部分替换为如下逻辑即可，示例使用|作为分隔符，可自行替换为逗号、短横线等你需要的符号：

CONCAT_WS('|', '+@cols+') as NewCol

拼接后的实际执行逻辑为CONCAT_WS('|', [breakfast],[lunch],[dinner]) as NewCol，输出效果示例：yes|no|yes。

问题2：透视结果别名无法外部访问的问题 #

报错原因 #

pivotexample2是exec执行的动态SQL内部的别名，仅在动态SQL的执行上下文内生效，当前会话的其他SQL批次无法直接访问该别名。

解决方案 #

方案1：将透视结果写入临时表（推荐，可复用性高）

直接在动态SQL中将透视结果写入临时表即可实现复用：

DECLARE @cols  AS VARCHAR(MAX)='';
DECLARE @query AS VARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) 

SET @query = '
SELECT * INTO ##pivotResult FROM (
    SELECT ID, '+@cols+', CONCAT_WS(''|'', '+@cols+' )as NewCol from 
    (
        select ID, category,answer from #temp
    ) pivotexample
    pivot 
    (
        max(Answer) for category in (' + @cols + ')
    ) as pivotexample2
) t
'
EXEC (@query)

-- 外部可直接访问全局临时表做关联、查询操作
SELECT * FROM ##pivotResult
-- 用完手动销毁避免占用
DROP TABLE ##pivotResult

注意：如果不需要跨会话访问，也可以提前在外部定义好与透视结果结构一致的普通临时表，在动态SQL内插入数据即可，避免全局临时表的名称冲突问题。

方案2：动态拼接所有逻辑（适合关联逻辑简单的场景）

如果你的后续关联、查询逻辑比较简单，可以把所有逻辑都拼接进动态SQL字符串，一起执行，也可以在动态SQL内用CTE封装透视逻辑：

SET @query = '
WITH pivotCTE AS (
    SELECT ID, '+@cols+', CONCAT_WS(''|'', '+@cols+' )as NewCol from 
    (
        select ID, category,answer from #temp
    ) pivotexample
    pivot 
    (
        max(Answer) for category in (' + @cols + ')
    ) as pivotexample2
)
-- 这里写你后续的关联、查询逻辑
SELECT * FROM pivotCTE 
JOIN 你的其他表 ON pivotCTE.ID = 你的其他表.ID
'
EXEC (@query)

内容的提问来源于stack exchange，提问作者Basil