PIVOT函数-动态列

大家好，欢迎来到IT知识分享网。

一、需求说明

原始表：

select * from pathogen_pro;

将pm_name的值转成对应的列，效果如下

二、PIVOT函数说明

PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]));

2-1、pivot固定列

select * from ( select * from pathogen_pro where pathogen_name_en is not null --and pathogen_type1 = '病毒' ) as a PIVOT ( max(is_pm) FOR pm_name IN ([臻达全+臻熙], [Dano-Seq多重病原体检测3.0+迪谱],[Dano-Seq多重病原体检测2.0+迪谱], [臻达敏+臻熙], [臻达准+臻熙]) -- 这里列出了所有可能的季度值 ) AS PivotTable;

2-2、pivot动态列

1、获取动态的列的值的字符串

DECLARE @cols AS NVARCHAR(MAX) SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(pm_name) FROM (SELECT pm_name FROM pathogen_pro) AS SubQuery PRINT @cols

2、获取结果

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); -- Step 1: 获取要转换为列的值的列表（即 Quarter 的唯一值） SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(pm_name) FROM (SELECT pm_name FROM pathogen_pro) AS SubQuery -- Step 2: 构建包含 PIVOT 查询的 SQL 字符串 SET @query = 'SELECT * FROM ( SELECT * FROM pathogen_pro where pathogen_name_en is not null ) x PIVOT ( max(is_pm) FOR pm_name IN (' + @cols + N') ) p '; -- 执行查询 --EXEC sp_executesql @query; print @query --执行 exec(@query)