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

The easiest way to accomplish what you are looking for is using a GROUP BY  and MAX(CASE) functionality.

Try below:

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)


select id, description,
MAX(CASE WHEN ET ='A' THEN ET_Status ELSE NULL END) AS ET_A_Status, 
MAX(CASE WHEN ET ='A' THEN ET_Date ELSE NULL END) AS ET_A_Status,
MAX(CASE WHEN ET ='A' THEN ET_IsValid ELSE NULL END) AS ET_A_ISValid,
MAX(CASE WHEN ET ='B' THEN ET_Status ELSE NULL END) AS ET_B_Status, 
MAX(CASE WHEN ET ='B' THEN ET_Date ELSE NULL END) AS ET_B_Status,  
MAX(CASE WHEN ET ='B' THEN ET_IsValid ELSE NULL END) AS ET_B_ISValid,
MAX(CASE WHEN ET ='C' THEN ET_Status ELSE NULL END) AS ET_C_Status, 
MAX(CASE WHEN ET ='C' THEN ET_Date ELSE NULL END) AS ET_C_Status,  
MAX(CASE WHEN ET ='C' THEN ET_IsValid ELSE NULL END) AS ET_C_ISValid
from #temp
group by id,Description

HTH

• Proposed as answer by Naomi NModerator 3 hours 46 minutes ago

Hi WebRelations,

To get the expected output, you can refer to below sample.

-- 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', 'NO'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) 
a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

 
SELECT * FROM #temp
SELECT 
ID,[Description]
,MAX(A) AS ET_A_Status,MAX(CASE WHEN A IS NOT NULL THEN ET_Date END) ET_A_Date,MAX(CASE WHEN A IS NOT NULL THEN ET_IsValid END) ET_A_IsValid
,MAX(B) AS ET_B_Status,MAX(CASE WHEN B IS NOT NULL THEN ET_Date END) ET_B_Date,MAX(CASE WHEN B IS NOT NULL THEN ET_IsValid END) ET_B_IsValid
,MAX(C) AS ET_C_Status,MAX(CASE WHEN C IS NOT NULL THEN ET_Date END) ET_C_Date,MAX(CASE WHEN C IS NOT NULL THEN ET_IsValid END) ET_C_IsValid
FROM #temp
PIVOT
(
MAX(ET_Status) FOR ET IN (A,B,C)
) p
GROUP BY ID,[Description]
 

For more complicated scenario, such as more distinct ETs, you would have to use a dynamic pivot.

If you have any question, feel free to let me know.

Issue is I have long list in ET column so is it possible we can do through dome dynamic SQL so I don't need to  hard code so what every value we have in ET column it will pick that value.

Thanks

Hi WebRelations,

A dynamic way for your reference.

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', 'NO'),
		(1,'Test','C', 'Ack','08/21/2015', 'Yes'),
		(2,'Test','C', 'Ack','08/21/2015', 'Yes'),
		(2,'Test2','C', 'Ack','08/21/2015', 'Yes')
		) 
a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)


DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT 
ID,[Description]';

DECLARE @maxPortion NVARCHAR(MAX); 
SELECT @maxPortion=(SELECT
','+'MAX('+ET+') AS ET_'+ET+'_Status,MAX(CASE WHEN '+ET+' IS NOT NULL THEN ET_Date END) ET_'+ET+'_Date,MAX(CASE WHEN '+ET+' IS NOT NULL THEN ET_IsValid END) ET_'+ET+'_IsValid'
FROM (SELECT DISTINCT ET FROM #temp) T ORDER BY ET  FOR XML PATH(''))
--SELECT @maxPortion
 
DECLARE @pvtCol NVARCHAR(MAX)
SELECT @pvtCol=(
SELECT STUFF((SELECT ','+ET FROM (SELECT DISTINCT ET FROM #temp) T ORDER BY ET  FOR XML PATH('')),1,1,''))  

SET @SQL= @SQL+@maxPortion+' FROM #temp
PIVOT
(
MAX(ET_Status) FOR ET IN ('+@pvtCol+')) p
GROUP BY ID,[Description]'

--SELECT @SQL

EXEC sp_executesql @SQL 


DROP TABLE #temp

If you have any question, feel free to let me know

Take a look at this article

 T-SQL: Dynamic Pivot on Multiple Columns

Thanks This works