动态列代SQL表行的基础上，(Dynamic columns generation on the b

我有四列的表ITEM_ID，颜色，大小，重量，我想告诉我的表行成一排类似物品1，颜色1，尺寸1，重量1，项目2，颜色2，...........， ITEM4，color4，尺寸4，重80磅...

以下是我的表

+---------+--------+--------+--------+
| 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      |...
+---------+--------+--------+--------+    +---------+--------+--------+--------+

提前致谢。

Answer 1:

为了得到这样的结果，你需要做的几件事情：

UNPIVOT当前数据
PIVOT从逆透视结果
使用动态SQL，因为你将有一个未知的行数

由于您使用的SQL Server 2005+可以使用CROSS APPLY来unpivot的数据，这个过程需要您的多列item_id ， color ， size和weight ，并将其转换为多行：

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拨弄演示。这给出了一个结果：

|       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代码将类似于以下内容：

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 |

Answer 2:

如果你想以编程方式做到这一点，你不知道的行数试试这个：

DECLARE @I INT, @END INT, @DATA nvarchar(max), @TEMPSTR nvarchar(max), @DynamicTableSQL nvarchar(max)
SET @I = 1
SET @DATA = ''
SET @TEMPSTR = ''

SELECT @END = MAX(item_id) from items


SET @DynamicTableSQL = 'DECLARE @DynamicTable TABLE('
WHILE @I <= @END
BEGIN
    --SELECT @I
    SET @TEMPSTR = (select CAST(item_id as nvarchar) + ',''' + color + ''',''' + size + ''',' + cast(weight as nvarchar) + ',' FROM items WHERE item_id = @I)
    SET @DynamicTableSQL = @DynamicTableSQL + 'item_id_' + CAST(@I AS VARCHAR(10))+ ' INT ,' + 'color_' + CAST(@I AS VARCHAR(10))+ ' NVARCHAR(15) ,'+ 'size_' + CAST(@I AS VARCHAR(10))+ ' NVARCHAR(15) ,'+ 'weight_' + CAST(@I AS VARCHAR(10))+ ' INT ,'
    SET @DATA += @TEMPSTR
    SELECT @I = @I + 1
END

SET @DynamicTableSQL = SUBSTRING(@DynamicTableSQL, 0, LEN(@DynamicTableSQL))
SET @DynamicTableSQL = @DynamicTableSQL + ') '
SET @DATA = SUBSTRING(@DATA, 0, LEN(@DATA))
SET @DynamicTableSQL = @DynamicTableSQL + ' INSERT INTO @DynamicTable VALUES (' + @DATA + ')'

SET @DynamicTableSQL = @DynamicTableSQL + ' SELECT * FROM @DynamicTable '

EXEC SP_EXECUTESQL @DynamicTableSQL

文章来源: Dynamic columns generation on the basis of rows of a table in sql