converting rows to columns dynamicaly based on conditions

Question

In the below image I have current data and expected data based on the first three columns the data need to get transpose

Table query:

drop table ##Test
CREATE TABLE ##Test
    (paymentid varchar(30),
     claimid  bigint,
     Lineno1  int,
      groupcode  varchar(2),
      Carc int,
      adjustmentamt  float,
     RARC  varchar(30),
       Edit1  varchar(30),
      Remit  varchar(30),
    )

    INSERT INTO ##Test
    (paymentid ,
     claimid  ,
     Lineno1  ,
      groupcode  ,
      Carc ,
      adjustmentamt  ,
     RARC  ,
       Edit1  ,
      Remit  )
VALUES
('QP18502291',14205893514,2,'CO',84,75.55,'N20','','D18')

('QP15930339',14127612308,1,'OA',23,263,'','ClaimDetail.COBAmt','')  ,
('QP15930339',14127612308,1,'OA',23,21.69,'','ClaimDetail.COBAmt',''),   

('QP18502291',14205893514,2,'OA',23,78.77,'','ClaimDetail.COBAmt',''),   
('QP18502291',14205893514,2,'OA',97,66.55,'N20','','D18')   


select * from ##Test

score 0 · Answer 1 · edited May 23 '17 at 10:26

Possible duplicate post and take a look at the solution.

See SQL Fiddle with Demo.

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by paymentid order by Lineno1) rn
                      from ##Test
                    ) d
                    cross apply
                    ( 
                      select 'groupcode', 1 union all
                      select 'carc', 2 union all
                      select 'adjustmentamt', 3 UNION ALL
                      SELECT 'rarc',4 UNION ALL
                      SELECT 'Edit1' ,5 UNION ALL
                      SELECT 'remit',6
                    ) c (col, so)
                    group by col, rn, so
                    order by rn, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT paymentid, claimid,Lineno1,' + @cols + '
             from 
             (
                select paymentid, claimid,Lineno1, col+cast(rn as varchar(10)) col, value
                from
                (
                  select paymentid, claimid,Lineno1, groupcode, carc, adjustmentamt,rarc,Edit1,remit,
                    row_number() over(partition by paymentid order by Lineno1) rn
                  from ##Test
                ) t
                cross apply
                (
                  select ''groupcode'', groupcode union all
                  select ''carc'', cast(carc as varchar(50)) union all
                  select ''adjustmentamt'', cast(adjustmentamt as varchar(50)) union all
                  select ''rarc'', rarc union all
                  select ''Edit1'' , Edit1 union all
                  select ''remit'' , remit 
                ) c (col, value)
            ) x pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query)

converting rows to columns dynamicaly based on conditions

1 Answers1