Pivotting two columns using dynamic sql, Sql Server

Pivotting two columns using dynamic sql

Language: Layout:

create table Table1 ([no] int, [code] int, [amount] decimal(9,2)) ; 
insert into Table1 values 
 (1, 61, 1714.00)
,(1, 23, 95.79)
,(1, 80, 31.00)
,(1, 61, 45.00)
,(2, 61, 1714.00)
,(2, 23, 95.79)
,(3, 80, 31.00)
,(4, 61, 45.00)
,(4, 61, 1714.00)
,(4, 23, 95.79)
,(4, 80, 31.00)
,(4, 61, 45.00)
;
declare @cols nvarchar(max);
declare @sql  nvarchar(max);
select @cols = stuff((
    select distinct 
      char(10)+'  , ' 
      + quotename('Code_'+rn)+'   = max(case when rn = '+rn+' then Code end)'
      + char(10)+'  , ' 
      + quotename('Amount_'+rn)+' = max(case when rn = '+rn+' then Amount end)'
    from (
      select No, Code
        , rn = convert(varchar(10),row_number() over (partition by No order by Code))
      from Table1 
      ) s
    order by 1
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,0,'');
  
select  @sql ='
select No '+@cols+'
from (
  select No, Code, Amount
    , rn = row_number() over (partition by No order by Code) 
  from Table1 
  ) sub
group by No';

select @sql;
exec(@sql);

View schema
Execution time: 0,03 sec, rows selected: 7, rows affected: 12, absolute service time: 0,2 sec

(No column name)

select No , [Code_1] = max(case when rn = 1 then Code end) , [Amount_1] = max(case when rn = 1 then Amount end) , [Code_2] = max(case when rn = 2 then Code end) , [Amount_2] = max(case when rn = 2 then Amount end) , [Code_3] = max(case when rn = 3 then Code end) , [Amount_3] = max(case when rn = 3 then Amount end) , [Code_4] = max(case when rn = 4 then Code end) , [Amount_4] = max(case when rn = 4 then Amount end) , [Code_5] = max(case when rn = 5 then Code end) , [Amount_5] = max(case when rn = 5 then Amount end) from ( select No, Code, Amount , rn = row_number() over (partition by No order by Code) from Table1 ) sub group by No

	No	Code_1	Amount_1	Code_2	Amount_2	Code_3	Amount_3	Code_4	Amount_4	Code_5	Amount_5
1	1	23	95,79	61	45,00	61	1714,00	80	31,00	NULL	NULL
2	2	23	95,79	61	1714,00	NULL	NULL	NULL	NULL	NULL	NULL
3	3	80	31,00	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	4	23	95,79	61	45,00	61	45,00	61	1714,00	80	31,00