CODEWITHSUNDEEP
sqlsql-servert-sql
razielx4crazy
razielx4crazy

Reputation: 159

How to convert rows to columns with multiple rows and nulls

I hope you can help me on this one: I want to create a query in order to have the records of a table as columns but this columns can have multiple rows.

Example:

| Id | Value  | ColumnName    |
| 1  | A      | Q1            |
| 2  | B      | Q2            |
| 3  | C      | Q2            |
| 4  | D      | Q2            |
| 5  | E      | Q3            |
| 6  | F      | Q3            |

This is what I want:

| Q1   | Q2   | Q3
| A    | B    | E 
| NULL | C    | F    
| NULL | D    | NULL

How can I get this result in one single query? (I really need to have this in a query because I need to create a view for this)

Thanks and regards!

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use conditional aggregation with row_number():

select max(case when columnname = 'Q1' then value end) as q1,
       max(case when columnname = 'Q2' then value end) as q2,
       max(case when columnname = 'Q3' then value end) as q3       
from (select t.*,
             row_number() over (partition by columnname order by id) as seqnum
      from t
     ) t
group by seqnum;

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

You can try using pivot

select pv.* from 
tablename
pivot
(max(Value) for columnname in ([Q1],[Q2],[Q3])) as pv

Upvotes: 1

Related Questions