PIVOT函数-动态列

一、需求说明

原始表：

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)