您可以使用动态sql创建col_name列表:

You can use dynamic sql to create the list of col_name:

declare @pivot_col varchar(max);
declare @sql       varchar(max);
select  @pivot_col = string_agg( cast(col_name as varchar(max)), ', ') within group ( order by col_name ) from ( select distinct col_name from tmp_table ) A;

set @sql = 'SELECT * 
            FROM   (
                      SELECT [ID], [Col_Name], [Col_Value] 
                      FROM tmp_table
                   ) AS a 
                   PIVOT 
                   (
                      MAX([Col_Value])
                      FOR [Col_Name] in (' +  @pivot_col + ' )
                   ) AS p 
                   ORDER BY [ID]';
exec ( @sql );

PIVOT/UNPIVOT运算符基于实体属性值模型(EAV)的原理构建. EAV模型背后的想法是，您可以扩展数据库实体而无需执行数据库架构更改.因此，EAV模型会将实体的所有属性存储在一个表中，作为键/值对.

The PIVOT / UNPIVOT operators are built on the principles of an Entity Attribute Value model (EAV). The idea behind an EAV model is that you can extend database entities without performing database schema changes. For that reason an EAV model stores all attributes of an entity in one table as key/value pairs.

如果这是您设计背后的想法，请使用我上面发布的动态sql查询，否则按照Gordon的建议，使用每个ID包含200列以上的常规记录.

If that is the idea behind your design then use the dynamic sql query i posted above, otherwise use a regular record with 200+ columns for each id, as suggested by Gordon.

您可以在此处阅读有关PIVOT/UNPIVOT运算符的性能的信息.

对于sql server 2016版本:

For sql server 2016 version:

declare @pivot_col varchar(max);
declare @sql       varchar(max);
select  @pivot_col = STUFF( (SELECT ',' + CAST(col_name AS VARCHAR(max)) AS [text()] FROM ( select distinct col_name from tmp_table ) A ORDER BY col_name FOR XML PATH('')), 1, 1, NULL);

set @sql = 'SELECT * 
            FROM   ( SELECT [ID], [Col_Name], [Col_Value] 
                     FROM tmp_table
                   ) AS a 
                   PIVOT 
                   (
                      MAX([Col_Value])
                      FOR [Col_Name] in (' +  @pivot_col + ' )
                   ) AS p 
                   ORDER BY [ID]';
exec ( @sql );