CODEWITHSUNDEEP
sqlsql-servert-sqlpivotsql-server-2014
Quan Le Duc Minh
Quan Le Duc Minh

Reputation: 33

Pivot group multi column SQL

In SQL, how can I merge multiple columns into one column with multiple rows? Example:

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

I want to get this table:

Exam  |  John |  Jill
------+-------+---------
22    |   21  |   20
M     |   F   |   M

Upvotes: 1

Views: 108

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can do this using a single query -- basically unpivot and conditional aggregation:

select max(case when v.name = 'Exam' then v.val end) as exam,
       max(case when v.name = 'John' then v.val end) as john,
       max(case when v.name = 'Jill' then v.val end) as jill
from t cross apply
     (values (t.name, cast(t.age as varchar(10)), 1),
             (t.name, t.gender, 2)
     ) v(name, val, which)
group by which;

Here is the SQL Fiddle.

You can convert the values to whatever character type you like for compatibility among the values. You want to put numeric values and strings in the same column, so they have to have the same type.

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

You can do like this.

Use two PIVOT query with UNION ALL to combine them

SELECT CAST(Exam AS VARCHAR(10)) Exam,
       CAST(Jill AS VARCHAR(10)) Jill,
       CAST(John AS VARCHAR(10)) John
FROM
(
    select age,name
    from T
) as x
PIVOT 
(
    MAX(Age) FOR name IN ([Exam],[John],[Jill])
)AS P1
UNION ALL
SELECT Exam,Jill,John FROM
(
    select name,gender
    from T
) as x
PIVOT 
(
    MAX(gender) FOR name IN ([Exam],[John],[Jill])
)AS P1

sqlfiddle:http://sqlfiddle.com/#!18/a437d/6

Upvotes: 4

Related Questions