I want to pivot a table from long to wide that has multiple id columns. I found solutions for one column but not really for multiple columns. The closest solution that I could adapt for one column was this one

My actual table has three _id columns and more permutations, so I really need a generic solution.

Based on the other solution in my link I was hoping something like this would work. I adjust the top part that creates the column names and this would work. I dont know how to realy adjust the bottom part that fetches the values.

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', '   'VarA_' convert(varchar(10),varA_id)   '_VarB_' convert(varchar(10),varB_id)
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')
select  @sql = '
 select Id, date, '   @cols   '
  from  (
    select Id, date, varA_id = ''v'' convert(varchar(10),varA_id), value
      from t
      ) as t
 pivot (sum([value]) for [varA_id] in ('   @cols   ') ) p'
select @sql
exec(@sql);

CodePudding user response：

The main problem with your dynamic sql?
It was that the name constructed in the source query didn't match the generated column names.

Here's a fix :

declare @cols varchar(max) = null;
declare @sql  nvarchar(max);

select @cols = concat(@cols ', ' char(10), quotename(concat('VarA_', varA_id, '_VarB_', varB_id))) 
from test
group by varA_id, varB_id
order by varA_id, varB_id;

-- select @cols as cols;

set @sql = 'select * ' char(10) 
  'from ( '  char(10) 
  ' select [date], [value], '  char(10) 
  ' concat(''VarA_'',varA_id,''_VarB_'',varB_id) as Col '  char(10) 
  ' from test '  char(10) 
  ') as src '  char(10) 
  'pivot (sum([value]) for Col in (' char(10)  @cols  char(10) ')) pvt';
 
-- select @sql as sql;

exec(@sql);

db<>fiddle here

CodePudding user response：

Ok my own solution so far is to add a help column and basically just do what the other questions does. I need to improve on this, so I dont add a column and I would like better names, but at least this shows what I want.

alter table t add help_col nvarchar(10)
Update t
set help_col=convert(varchar(10),varA_id) convert(varchar(10),varB_id)

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', '   'v' convert(varchar(10),help_col)
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')
select  @sql = '
 select date, '   @cols   '
  from  (
    select date, help_col = ''v'' convert(varchar(10),help_col), value
      from t
      ) as t
 pivot (sum([value]) for [help_col] in ('   @cols   ') ) p'
select @sql
exec(@sql);

Which results in

   select date, v11, v12, v13, v21, v22, v23, v31, v32, v33    from  (      select date, help_col = 'v' convert(varchar(10),help_col), value        from t        ) as t   pivot (sum([value]) for [help_col] in (v11, v12, v13, v21, v22, v23, v31, v32, v33) ) p

which yields

date        v11 v12 v13 v21 v22 v23 v31 v32 v33
2005-01-20  197 58  90  210 133 67  87  87  87