SQLTeam.com | Weblogs | Forums

Pivot with Multiple Columns


#1

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date - ET_A_IsValid - ET_B_Status - ET_B_Date - ET_B_IsValid - ET_C_Status - ET_C_Date - ET_C_IsValid
1 - Test - 'Ack' - '2015-08-15 00:00:00.000' - 'Yes' - 'Nack' - '2015-08-17 00:00:00.000' - 'Yes' - 'Ack' - '2015-08-21 00:00:00.000' - 'Yes'

Thanks


#2
select ID , Description ,
[ET_A_Status] = max(case when ET = 'A' then ET_Status end),
[ET_A_Date] = max(case when ET = 'A' then ET_Date end),
[ET_A_IsValid] = max(case wen ET = 'A' then ET_isValid),
[ET_B_Status] = max(case when ET = 'B' then ET_Status end),
[ET_B_Date] = max(case when ET = 'B' then ET_Date end),
[ET_B_IsValid] = max(case wen ET = 'B' then ET_isValid end),
.....
from   #temp
group by ID, Description

#3

For dynamic values of ET, you can try this approach http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx


#4

Guess I'll throw my hat into the ring, as well.

http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/