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