CODEWITHSUNDEEP
sqlsql-serverpivottranspose
Spjcc
Spjcc

Reputation: 19

SQL Transpose and group some columns

I want to transpose and group some columns from: 

Agent     Date       Interval     EmailTime     PhoneTime
John      1-1-2017    00:00        00:15:00      NULL
John      1-1-2017    00:15        00:10:00      00:05:00
John      1-1-2017    00:30        NULL          00:15:00

To:

Agent     Date       Interval      State          Duration
John      1-1-2017    00:00        EmailTime      00:15:00
John      1-1-2017    00:15        EmailTime      00:10:00
John      1-1-2017    00:15        PhoneTime      00:05:00
John      1-1-2017    00:30        PhoneTime      00:05:00

Any suggestions?

Upvotes: 0

Views: 136

Answers (2)

ilhan kaya
ilhan kaya

Reputation: 51

you can use this code blcok:

select agent ,date ,interval 'EmailTime' as state ,emailtime as duration from table where emailtime is not null union all select agent ,date ,interval 'PhoneTime' as state ,phonetime as duration from table where phonetime is not null union all select agent ,date ,interval 'NULL' state ,phonetime as duration from table where emailtime is null and phonetime is null

Upvotes: 0

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

you dont need a pivot. it can be achieved with a union all query.

Select Agent, Date, Interval    'EmailTime' State,  EmailTime     from table where EmailTime is not null 
union all 
Select Agent, Date, Interval    'PhoneTime' State,  PhoneTime     from table where PhoneTime is not null

Upvotes: 1

Related Questions