Answered by:
Transpose column data to rows

Question
-
Hi, I want to get max(effdate) timeline of combination (pid-tp-op-effdate) and want all code of 'Op' and effdate column in one column with comma (transpose column to rows).
drop table #MaxP
create table #MaxP (pid char(10), tp char(4), Op char(20), effdate datetime, Did integer,zp char(2))
Insert into #maxp values ('A1','HL','01','1901-01-01',12,'JJ')
Insert into #maxp values ('A1','HL','02','1901-01-01',12,'JJ')
Insert into #maxp values ('A1','HL','03','1901-01-01',12,'JJ')
Insert into #maxp values ('A1','HL','04','1901-01-01',12,'JJ')
Insert into #maxp values ('A1','HL','02','2019-01-01',12,'NN')
Insert into #maxp values ('A1','HL','03','2019-01-01',12,'NN')
Insert into #maxp values ('A1','HL','04','2019-01-01',12,'NN')
Insert into #maxp values ('B1','DD','01','1901-01-01',14,'OO')
Insert into #maxp values ('B1','DD','99','1901-01-01',14,'OO')
Insert into #maxp values ('B1','DD','01','2018-01-01',16,'KK')
Insert into #maxp values ('B1','DD','99','2018-01-01',16,'KK')
Select * from #MaxP order by pid
pid tp Op effdate Did Zp
A1 HL 01,02,03,04 1901-01-01,2019-01-01 12 NN
B1 HL 01,99 2018-01-01 12 KK
Answers
-
create table #MaxP (pid char(10), tp char(4), Op char(20), effdate datetime, Did integer,zp char(2)) Insert into #maxp values('A1','HL','01','1901-01-01',12,'JJ') , ('A1','HL','02','1901-01-01',12,'JJ') , ('A1','HL','03','1901-01-01',12,'JJ') , ('A1','HL','04','1901-01-01',12,'JJ') , ('A1','HL','02','2019-01-01',12,'NN') , ('A1','HL','03','2019-01-01',12,'NN') , ('A1','HL','04','2019-01-01',12,'NN') , ('B1','DD','01','1901-01-01',14,'OO') , ('B1','DD','99','1901-01-01',14,'OO') , ('B1','DD','01','2018-01-01',16,'KK') , ('B1','DD','99','2018-01-01',16,'KK') ;with mycte as (SELECT t1.pid,t1.tp, Stuff(( SELECT ',' + Cast(t2.Op as varchar(3)) FROM (Select Distinct pid,tp,op FROM #MaxP) t2 WHERE t2.pid = t1.pid and t2.tp = t1.tp ORDER BY op FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS Ops From (Select Distinct pid,tp,op FROM #MaxP) t1 GROUP BY t1.pid,t1.tp) ,mycte2 as ( SELECT t1.pid,t1.tp, Stuff(( SELECT ',' + format(t2.effdate,'yyyy-MM-dd') FROM (Select Distinct pid,tp,effdate FROM #MaxP) t2 WHERE t2.pid = t1.pid and t2.tp = t1.tp ORDER BY effdate FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS effdates From (Select Distinct pid,tp,effdate FROM #MaxP) t1 GROUP BY t1.pid,t1.tp ) Select m1.pid,m1.tp,m1.Ops,m2.effdates from mycte m1 join mycte2 m2 on m1.pid=m2.pid and m1.tp=m2.tp drop table #MaxP
- Marked as answer by Khan_K Friday, July 19, 2019 5:32 PM
All replies
-
-
-
What about effdate and Did for B1 in your result?
- Edited by Jingyang LiModerator Friday, July 19, 2019 3:11 PM
-
create table #MaxP (pid char(10), tp char(4), Op char(20), effdate datetime, Did integer,zp char(2)) Insert into #maxp values('A1','HL','01','1901-01-01',12,'JJ') , ('A1','HL','02','1901-01-01',12,'JJ') , ('A1','HL','03','1901-01-01',12,'JJ') , ('A1','HL','04','1901-01-01',12,'JJ') , ('A1','HL','02','2019-01-01',12,'NN') , ('A1','HL','03','2019-01-01',12,'NN') , ('A1','HL','04','2019-01-01',12,'NN') , ('B1','DD','01','1901-01-01',14,'OO') , ('B1','DD','99','1901-01-01',14,'OO') , ('B1','DD','01','2018-01-01',16,'KK') , ('B1','DD','99','2018-01-01',16,'KK') ;with mycte as (SELECT t1.pid,t1.tp, Stuff(( SELECT ',' + Cast(t2.Op as varchar(3)) FROM (Select Distinct pid,tp,op FROM #MaxP) t2 WHERE t2.pid = t1.pid and t2.tp = t1.tp ORDER BY op FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS Ops From (Select Distinct pid,tp,op FROM #MaxP) t1 GROUP BY t1.pid,t1.tp) ,mycte2 as ( SELECT t1.pid,t1.tp, Stuff(( SELECT ',' + format(t2.effdate,'yyyy-MM-dd') FROM (Select Distinct pid,tp,effdate FROM #MaxP) t2 WHERE t2.pid = t1.pid and t2.tp = t1.tp ORDER BY effdate FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS effdates From (Select Distinct pid,tp,effdate FROM #MaxP) t1 GROUP BY t1.pid,t1.tp ) Select m1.pid,m1.tp,m1.Ops,m2.effdates from mycte m1 join mycte2 m2 on m1.pid=m2.pid and m1.tp=m2.tp drop table #MaxP
- Marked as answer by Khan_K Friday, July 19, 2019 5:32 PM
-
-
-