MS SQL Pivot dat from long with multiple columns

Question

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 T-SQL PIVOT data from long form to wide by a date

My table looks more or less like this,

create table t (id int, date date, varA_id int, VarB_id int, value int)
insert into t values
 (1,'2005-01-20',1, 1,197)
,(2,'2005-01-20',1,2,58)
,(3,'2005-01-20',1,3,90)
,(4,'2005-01-20',2,1,210)
,(5,'2005-01-20',2,2,133)
,(6,'2005-01-20',2,3,67)
,(7,'2005-01-20',3,1,87)
,(8,'2005-01-20',3,2,87)
,(9,'2005-01-20',3,3,87)

Actually without the date, but that's fine. I want to spread in a way that I get columns for each permutation of VarA_id and VarB_id

So my expected result would look like this

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);

Answer 1

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);

date	VarA_1_VarB_1	VarA_1_VarB_2	VarA_1_VarB_3	VarA_2_VarB_1	VarA_2_VarB_2	VarA_2_VarB_3	VarA_3_VarB_1	VarA_3_VarB_2	VarA_3_VarB_3
2005-01-20	197	58	90	210	133	67	87	87	87

db<>fiddle here

Answer 2

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