使用PIVOT将200+行转换为SQL Server中的列 [英] Converting 200+ rows to column in SQL Server using PIVOT

问题描述

我已经做了一些研究，到目前为止，这对我来说很有意义:

I have done some research and so far, this one makes sense to me:

将行值转换为SQL Server中的列(PIVOT)

但是，我想知道是否有任何方法可以不必一一声明所需的列，因为我有200多个要检索的列.

However, I am wondering if there is any way to do this without having to declare the needed columns one by one, as I have more than 200 columns to retrieve.

这是我的桌子一览表:

ID   | Col_Name   | Col_Value    
------------------------------
1    | Item_Num   | 12345
2    | Item_Date  | 34567
3    | Item_Name  | 97454
4    | Item_App1  | 234567
5    | Item_App2  | 345678

请注意，我对Col_Name有200多个不同的值，并且希望它成为列"字段.

Take note that I have 200+ distinct values for Col_Name and I want it to be Column fields.

到目前为止，我正在使用这一列，但仅用于5列:

I am using this one as of now but only for 5 columns:

SELECT * FROM 
(
   SELECT [ID], [Col_Name], [Col_Value] 
   FROM myTable
) AS a 
PIVOT 
(
   MAX([Col_Value])
   FOR [Col_Name] in ([Item_Num], [Item_Date], [Item_Name], [Item_App1], [Item_App2])
) AS p 
ORDER BY [ID]

考虑性能，有什么方法可以做到吗?预先感谢.

Is there any way this could be done considering the performance? Thanks in advance.

推荐答案

您可以使用动态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 );

这篇关于使用PIVOT将200+行转换为SQL Server中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！