SQL Server does not allow multiple columns

Question

I am trying to rotate several columns around it to get up to 3 columns (vault, column name, value)

for example:

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

will become:

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

I searched Google a bit, but did not find a similar situation - especially since the rotation axis seems to be running in the opposite direction, like what I'm trying to accomplish.

Answer 1

Converting columns to rows is called UNPIVOT . You did not specify which version of SQL Server you are using, but there are several 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+, 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 can also use CROSS APPLY with 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 you want. You will notice that I needed to arrange the age column in varchar . This is because the data type / length (in univot) of the columns must be the same as you convert them to a single column in the final result.

Answer 2

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