基于sql中表行的动态列生成 [英] Dynamic columns generation on the basis of rows of a table in sql

问题描述

我有一个包含四列item_id，颜色，大小，重量的表，我想将我的表行显示为一行，例如item1，color1，size1，weight1，item2，color2，......... ..，item4，color4，size4，weight4 ...

I have a table with four columns item_id, color, size, weight, I want to show my table rows into one row like item1,color1,size1,weight1,item2,color2,...........,item4,color4,size4,weight4 ...

以下是我的桌子

+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |

我想要的结果将是

+---------+--------+--------+--------++---------+--------+--------+
| item_id1| color1| size1 | weight1| item_id2 | color2  | size2   | weight2 |....
+---------+--------+--------+--------+---------+--------+--------+---------------
|       1 | blue   | large| 65     |      2   | orange  | large   | 57      |...
+---------+--------+--------+--------+    +---------+--------+--------+--------+

提前谢谢.

推荐答案

为了获得此结果，您需要做一些事情:

In order to get this result, you will need to do a few things:

• UNPIVOT 当前数据
• PIVOT 的结果坚不可摧
• 使用动态SQL，因为您将拥有未知数量的行

• UNPIVOT the current data
• PIVOT the result from the unpivot
• use dynamic SQL since you will have an unknown number of rows

由于使用的是SQL Server 2005+，因此可以使用CROSS APPLY取消数据透视，此过程将使用item_id，color，size和weight的多列并将它们转换为多行:

Since you are using SQL Server 2005+ you can use CROSS APPLY to unpivot the data, this process takes your multiple columns of item_id, color, size and weight and converts them into multiple rows:

select col+'_'+cast(seq as varchar(50)) col,
     value
from 
(
   select item_id as seq, item_id, color, size, weight
   from yourtable
) d
cross apply
(
   values
    ('item_id', cast(item_id as varchar(50))),
    ('color', color),
    ('size', size),
    ('weight', cast(weight as varchar(50)))
) c (col, value);

请参见带有演示的SQL小提琴.得到的结果是:

See SQL Fiddle with Demo. This gives a result:

|       COL |  VALUE |
----------------------
| item_id_1 |      1 |
|   color_1 |   blue |
|    size_1 |  large |
|  weight_1 |     65 |
| item_id_2 |      2 |
|   color_2 | orange |
|    size_2 |  large |
|  weight_2 |     57 |
| item_id_3 |      3 |

从结果中可以看到，现在基于原始数据有多行. COL值是将用于PIVOT的值.完整的动态SQL代码将类似于以下内容:

As you can see from the result you now have multiple rows in based off your original data. The COL values are the values that you will use to PIVOT. The full dynamic SQL code will be similar to the following:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+'_'+cast(item_id as varchar(10))) 
                    from yourtable
                    cross apply
                    (
                      select 'item_id', 0 union all
                      select 'color', 1 union all
                      select 'size', 2 union all
                      select 'weight', 3 
                    ) c (col, so)
                    group by item_id, col, so
                    order by item_id, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
                select col+''_''+cast(seq as varchar(50)) col,
                  value
                from 
                (
                  select item_id as seq, item_id, color, size, weight
                  from yourtable
                ) d
                cross apply
                (
                  values
                    (''item_id'', cast(item_id as varchar(50))),
                    (''color'', color),
                    (''size'', size),
                    (''weight'', cast(weight as varchar(50)))
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

请参见带演示的SQL小提琴.最终结果是:

| ITEM_ID_1 | COLOR_1 | SIZE_1 | WEIGHT_1 | ITEM_ID_2 | COLOR_2 | SIZE_2 | WEIGHT_2 | ITEM_ID_3 | COLOR_3 | SIZE_3 | WEIGHT_3 | ITEM_ID_4 | COLOR_4 | SIZE_4 | WEIGHT_4 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|         1 |    blue |  large |       65 |         2 |  orange |  large |       57 |         3 |     red |  small |       12 |         4 |  violet | medium |       34 |

这篇关于基于sql中表行的动态列生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！