Posted By: Anonymous
How do I simply switch columns with rows in SQL?
Is there any simple command to transpose?
ie turn this result:
Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 1
Red | Green | Blue Paul 1 8 2 John 5 4 2 Tim 1 3 9 Eric 3 5 1
PIVOT seems too complex for this scenario.
There are several ways that you can transform this data. In your original post, you stated that
PIVOT seems too complex for this scenario, but it can be applied very easily using both the
PIVOT functions in SQL Server.
However, if you do not have access to those functions this can be replicated using
UNION ALL to
UNPIVOT and then an aggregate function with a
CASE statement to
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int); INSERT INTO yourTable ([color], [Paul], [John], [Tim], [Eric]) VALUES ('Red', 1, 5, 1, 3), ('Green', 8, 4, 3, 5), ('Blue', 2, 2, 9, 1);
Union All, Aggregate and CASE Version:
select name, sum(case when color = 'Red' then value else 0 end) Red, sum(case when color = 'Green' then value else 0 end) Green, sum(case when color = 'Blue' then value else 0 end) Blue from ( select color, Paul value, 'Paul' name from yourTable union all select color, John value, 'John' name from yourTable union all select color, Tim value, 'Tim' name from yourTable union all select color, Eric value, 'Eric' name from yourTable ) src group by name
UNION ALL performs the
UNPIVOT of the data by transforming the columns
Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function
sum() with the
case statement to get the new columns for each
Unpivot and Pivot Static Version:
PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:
select name, [Red], [Green], [Blue] from ( select color, name, value from yourtable unpivot ( value for name in (Paul, John, Tim, Eric) ) unpiv ) src pivot ( sum(value) for color in ([Red], [Green], [Blue]) ) piv
The inner query with the
UNPIVOT performs the same function as the
UNION ALL. It takes the list of columns and turns it into rows, the
PIVOT then performs the final transformation into columns.
Dynamic Pivot Version:
If you have an unknown number of columns (
Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to
UNPIVOT and then
DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('yourtable') and C.name <> 'color' for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT ',' + quotename(color) from yourtable t FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select name, '[email protected]+' from ( select color, name, value from yourtable unpivot ( value for name in ('[email protected]+') ) unpiv ) src pivot ( sum(value) for color in ('[email protected]+') ) piv' exec(@query)
The dynamic version queries both
yourtable and then the
sys.columns table to generate the list of items to
PIVOT. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of
names this will generate the list at run-time.
All three queries will produce the same result:
| NAME | RED | GREEN | BLUE | ----------------------------- | Eric | 3 | 5 | 1 | | John | 5 | 4 | 2 | | Paul | 1 | 8 | 2 | | Tim | 1 | 3 | 9 |