Sign in
 

none
Transpose column data to rows RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    Please check your expected result. Thanks.
    Friday, July 19, 2019 2:58 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    What about effdate  and Did for B1 in your result?
    Friday, July 19, 2019 3:10 PM
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    It is not consistent with A1 and B1 rows in your result. Check again.
    Friday, July 19, 2019 3:31 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you Sir
    Friday, July 19, 2019 5:32 PM
    |