Dynamic columns generation on the basis of rows of

Question

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

Following is my table

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

My desired result will be

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

Thanks in advance.

Answer 1

If you want to do it programmatically and you don't know the number of rows try this :

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

Answer 2

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

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

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);

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 |

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);

See SQL Fiddle with Demo. The final result is:

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