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