SQL Server unpivot multiple columns

Question

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.

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