'SQL Server unpivot multiple columns

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.

sqlsql-serverunpivot


Solution 1:[1]

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.

Solution 2:[2]

SELECT name, column, value
FROM (SELECT name, age, gender
FROM table) src
UNPIVOT (value FOR column IN (age, gender)) pvt

Solution 3:[3]

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!

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 L Vermeulen
Solution 3 BernhardP
Solution 4 Jeremy Caney

Related Questions