Pivot multiple Rows into multiple column

Transact-SQL
, ,
1

Hi,

Hi have a data that need to pivot and create multiple column. my query is working if I exclude the good and bad qty but not working if added the two column. however this should be included in the column as one of the reference to validate the faildesc. please see below sample data and ddl including desired result. thank you.

declare @t table
 (id nvarchar(10),
 faildesc nvarchar(20),
 goodqty int,
 badqty int
 )

INSERT	@t VALUES('0001','Dis:abc',0,1)
INSERT	@t VALUES('0001','Enclose :aaaa',0,1)
INSERT	@t VALUES('0001','Log:123',0,1)

INSERT	@t VALUES('0002','Dis:1234',1,0)
INSERT	@t VALUES('0002','Enclose :gggg',0,1)
INSERT	@t VALUES('0002','Log:222',1,0)

INSERT	@t VALUES('0003','',1,0)
INSERT	@t VALUES('0003','Enclose :ffff',1,0)
INSERT	@t VALUES('0003','Log:222',1,0)

INSERT	@t VALUES('0004','',1,0)
INSERT	@t VALUES('0004','',1,0)
INSERT	@t VALUES('0004','Log:333',1,0)

;with cte
as(
select 
		id,
		faildesc,
		goodqty,
		badqty,
		row_number() over(partition by id order by faildesc asc) rn
from	@t
)
select
		id,
		--goodqty,
		--badqty,
		ValueData1 as disposition, ValueData2 as Enclose, ValueData3 as [log]
from
(
select
		id,
		--goodqty,
		--badqty,
		'ValueData'+cast(rn as varchar(10)) rn,
		faildesc
from	cte
)d
pivot
(
  max(faildesc)
  for rn in (ValueData1, ValueData2, ValueData3)
) piv;




id-----disposition---Enclose--------Log------good_dis_qty---good_Enclose_qty----good_Log_qty--bad_dis_qty--bad_Enclose_qty--bad_Log_qty
---------------------------------------------------------------------------------------------------------------------------------------
0001---Dis:abc-------Enclose :aaaa--Log:123-----0------------------0------------------0----------1---------------1------------1
0002---Dis:1234------Enclose :gggg--Log:222-----0------------------0------------------0----------1---------------1------------1
0003-----------------Enclose :ffff--Log:222-----1------------------0------------------0----------0---------------1------------1
0004---Dis:1111---------------------Log:3
2 
declare @t table
(id nvarchar(10),
faildesc nvarchar(20),
goodqty int,
badqty int,
category nvarchar(1),
rn int
)

INSERT	@t VALUES('0001','Dis:abc',0,1,'D',1)
INSERT	@t VALUES('0001','Enclose :gggg',0,1,'E',2)
INSERT	@t VALUES('0001','Log:xxxx',0,1,'L',3)
INSERT	@t VALUES('0002','Dis:1234',1,0,'D',1)
INSERT	@t VALUES('0002','Enclose :gggg',0,1,'E',2)
INSERT	@t VALUES('0002','Log:xxxx',1,0,'L',3)
INSERT	@t VALUES('0003','',1,0,'D',1)
INSERT	@t VALUES('0003','Enclose :gggg',1,0,'E',2)
INSERT	@t VALUES('0003','Log:xxxx',1,0,'L',3)


select distinct t.id,
		 d.faildesc as d_faildesc, d.goodqty as d_goodqty, d.badqty as d_badqty,
		 e.faildesc as e_faildesc, e.goodqty as e_goodqty, e.badqty as e_badqty,
		 l.faildesc as l_faildesc, l.goodqty as l_goodqty, l.badqty as l_badqty
from @t t
		outer apply (
					select  id, faildesc, goodqty, badqty, category, rn
					from	@t
					where   category='D' and rn=1
					and t.id=id	
					) d

		outer apply (
					select  id, faildesc, goodqty, badqty, category, rn
					from	@t
					where   category='E' and rn=2
					and t.id=id	
					) e

		outer apply (
					select  id, faildesc, goodqty, badqty, category, rn
					from	@t
					where   category='L' and rn=3
					and t.id=id	
					) l`Preformatted text`