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 *
    , 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,02 sec, rows selected: 7, rows affected: 12, absolute service time: 0,17 sec

(No column name)

select No , [Code_1] = max(case when rn = 1 then Code end) , [Amount1] = max(case when rn = 1 then Amount end) , [Code_2] = max(case when rn = 2 then Code end) , [Amount2] = max(case when rn = 2 then Amount end) , [Code_3] = max(case when rn = 3 then Code end) , [Amount3] = max(case when rn = 3 then Amount end) , [Code_4] = max(case when rn = 4 then Code end) , [Amount4] = max(case when rn = 4 then Amount end) , [Code_5] = max(case when rn = 5 then Code end) , [Amount5] = max(case when rn = 5 then Amount end) from ( select * , rn = row_number() over (partition by No order by Code) from Table1 ) sub group by No

	No	Code_1	Amount1	Code_2	Amount2	Code_3	Amount3	Code_4	Amount4	Code_5	Amount5
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