none
Transpose column data to rows RRS feed

  • 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

    Friday, July 19, 2019 2:50 PM

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
    Friday, July 19, 2019 3:27 PM
    Moderator

All replies

  • Please check your expected result. Thanks.
    Friday, July 19, 2019 2:58 PM
    Moderator
  • Hi, Sorry - I have corrected my expected result.

    pid   tp   Op                effdate                            Did    Zp
    A1    HL   01,02,03,04  1901-01-01,2019-01-01  12     NN
    B1    DD   01,99           2018-01-01                     16     KK

    Friday, July 19, 2019 3:05 PM
  • What about effdate  and Did for B1 in your result?
    Friday, July 19, 2019 3:10 PM
    Moderator
  • 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
    Friday, July 19, 2019 3:27 PM
    Moderator
  • The effdate-2018-01-01 is max(effdate) and Did-16 is of current timeline-2018-01-01
    The combination is (pid-tp-op-effdate) to get the max(effdate) timeline
    Friday, July 19, 2019 3:29 PM
  • It is not consistent with A1 and B1 rows in your result. Check again.
    Friday, July 19, 2019 3:31 PM
    Moderator
  • Thank you Sir
    Friday, July 19, 2019 5:32 PM