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

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 |