Monday, November 25, 2013

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
into this:
        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.
share|improve this question
1 
add comment

2 Answers

There are several ways that you can transform this data. In your original post, you stated that PIVOTseems too complex for this scenario, but it can be applied very easily using both the UNPIVOT andPIVOT functions in SQL Server.
However, if you do not have access to those functions this can be replicated using UNION ALL toUNPIVOT and then an aggregate function with a CASE statement to PIVOT:
Create Table:
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
The 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 color.
Unpivot and Pivot Static Version:
Both the UNPIVOT and 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 PIVOT:
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, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)
The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and 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 colors and/or 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 |
share|improve this answer
 
I should have emphasized that I want a method to transpose the results of a query on a database. A simple transpose type command that can be executed on the result set from a query without having to know anything about the query itself, or the columns, tables etc it is extracting info from. Something like: (TRANSPOSE (SELECT......)) In my example given above, imagine that the first table is a result set generated from a very complex query involving multiple tables, unions, pivots etc. Yet the result is a simple table. I just want to flip the columns with rows in this result. –  edezzie Nov 14 '12 at 18:46
 
I'm passing the result to a c# DataTable. I can build a simple method 'Transpose(Datatable dt)' to do it there, but is there anything in SQL to do this. –  edezzie Nov 14 '12 at 18:47
 
@edezzie there is not a simple way in SQL to do this. You could probably alter the dynamic version to pass in the table name and pull the info from the system tables. –  bluefeet Nov 14 '12 at 18:55
add comment

6 comments:

Unknown said...

www0626
uggs outlet
ray ban sunglasses
polo outlet
wizards jerseys
jack wolfskin
air huarache
uggs outlet
longchamp pliage
cheap nhl jerseys
moncler jackets






jeje said...

Souvenez-vous que air jordan future rouge pas cher votre produit ne devrait pas être aussi facile à modifier en apparence que votre air jordan 5 low knicks modèle, alors construisez-le en fonction de vos besoins et remplacez-le au lieu achat nike flyknit pas cher d'avoir un sujet. La réelle Nike Atmosphère traditionnelle BW Asics Gel Lyte 3 Femme Grise qui est initialement appelé le réel. Avec un seul avec les air jordan pas cher dernières technologies, les chaussures «FlyKnit», Nike continue à l'améliorer de la même manière. basket nike air max 1 fb woven

zzyytt said...

yeezy boost
air max 2019
adidas superstar
kd shoes
lacoste outlet
air max 90
stephen curry 5
jordan 11 retro
nike cortez
retro jordans

jasonbob said...

yeezy boost 350
yeezy shoes
nike shox for women
off white jordan 1
pg 1
golden goose
jordan shoes
longchamp outlet
paul george
pandora

Unknown said...

great site find more info check this site out Gucci Dolabuy Homepage my link

tasi said...

h5r34g7s70 c0y27x4b75 x0a98z7r14 k8l60h6i80 l0z72k2c25 v0d15y9o80