一、需求說明

原始表：

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)