21

I'm trying to pivot a table around it's many columns to get to 3 columns (pivot, column name, value)

so for example:

name  |  age  |  gender
------+-------+---------
John  |   20  |    M
Jill  |   21  |    F

would become:

name | column | value
-----+--------+-------
John |  age   |   20
John | gender |   M
Jill |  age   |   21
Jill | gender |   F

I've googled quite a bit but haven't found a similar situation - especially since the pivot seems to be done in the opposite direction as what I'm trying to accomplish.

Taryn
  • 242,637
  • 56
  • 362
  • 405
mathematician
  • 1,942
  • 5
  • 19
  • 22

4 Answers4

46

The conversion of columns into rows is called an UNPIVOT. You didn't specify what version of SQL Server you are using but there are several different ways to get the result.

You can use SELECT with UNION ALL:

SELECT name, 'age' as column, cast(age as varchar(10)) as value
FROM yourtable
UNION ALL
SELECT name, 'gender' as column, gender as value
FROM yourtable;

If you are using SQL Server 2005+, then you can use the UNPIVOT function:

SELECT name, column, age
FROM
(
  SELECT 
    name, 
    age = cast(age as varchar(10)), 
    gender
  FROM yourtable
) d
UNPIVOT
(
  value
  for column in (age, gender)
) unpiv;

Finally, instead of the UNPIVOT function you could also use CROSS APPLY with either VALUES (2008+) or UNION ALL:

SELECT name, column, age
FROM yourtable
CROSS APPLY
(
  VALUES
    ('age', cast(age as varchar(10)),
    ('gender', gender)
) c (column, value);

Any of these versions will give you the result that you want. You'll note that I had to cast the age column to a varchar. This is because the datatype/length (in unpivot) of the columns must be the same since you will be transforming them into a single column in the final result.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Might want to point out, in my testing on a SQL 2008 server, that the max_length also needed to match, so if Gender was char(1), it also needed to be cast for the unpivot to happen. Haven't tried with the outer apply, though. – Jaaz Cole Jul 18 '14 at 15:33
  • so what you're saying is, in my search for how to use pivot, since i found only the opposite applications, i should have looked at UNpivot... brilliant!! : ) thanks! – mathematician Jul 18 '14 at 15:43
  • @JaazCole It is only UNPIVOT that requires the datatype to be the same length - see this on [DBA.SE](http://dba.stackexchange.com/questions/54353/why-does-sql-server-require-the-datatype-length-to-be-the-same-when-using-unpivo) – Taryn Jul 18 '14 at 15:49
  • 4
    that `CROSS APPLY` trick is awesome. it actually seems to be more flexible and IMO more readable than using `UNPIVOT` – jtate Nov 07 '19 at 21:26
  • 1
    `CROSS APPLY` is a great solution but I suspect you may need to quote `[column]` – CervEd Nov 06 '21 at 14:43
2
SELECT name, column, value
FROM (SELECT name, age, gender
FROM table) src
UNPIVOT (value FOR column IN (age, gender)) pvt
L Vermeulen
  • 39
  • 9
0

You can do it easily with the 'WITH'-Clause and a generated helper-column. In
the first cte you generate temporary column, in the second, you unpivot it and in the the third you put it together again. Voila!

BernhardP
  • 1
0
WITH cte1 AS (SELECT ROW_NUMBER() OVER (ORDER BY adl_id, ma_id) AS rk, 
adl_id, 
ma_id, 
ISNULL(CONVERT(CHAR,fosa_id),'') fosa_id,
ISNULL(CONVERT(CHAR,kdah_id),'') kdah_id,
ISNULL(CONVERT(CHAR,lb_id),'') lb_id,
ISNULL(CONVERT(CHAR,sprach_id),'') sprach_id
FROM tm_lieferant WHERE adl_id=1004)
,cte2 AS (SELECT rk, fieldname, [value] FROM (
SELECT rk, fosa_id, kdah_id, lb_id, sprach_id FROM cte1 ) p
UNPIVOT([value] FOR fieldname IN (fosa_id, kdah_id, lb_id, sprach_id
)) AS unpvt)
,cte3 AS (SELECT cte1.adl_id, cte1.ma_id, cte2.fieldname, cte2.value FROM cte2 INNER JOIN cte1 ON cte2.rk=cte1.rk)
SELECT * FROM cte3
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
BernhardP
  • 1
  • This question already has an answer with over 42 upvotes. Why do you prefer you approach over theirs? Can you [edit] your answer to include an explanation of what you're doing and why you think it's the best approach? – Jeremy Caney Apr 29 '22 at 00:41