SQL字段名称的转换，请大咖们支招！谢谢！

[KnitsEDI]

create table #a(id varchar(10),des1 varchar(20),des2 varchar(20))
create table #b(id varchar(10),s1 varchar(10),s2 varchar(10),s3 varchar(10))
insert into #a
select 'po1','s1','A'
UNION
select 'po1','s2','B'
UNION
select 'po1','s3','C'

insert into #b
select 'po1','11','111','1111'
UNION
select 'po1','22','222','2222'
UNION
select 'po1','33','333','3333'

DROP TABLE #a
DROP TABLE #B

想要这效果显示，把#a表中的对应的内容用来做字段名称，请各位大神们支个招。
ID A B C
po1 11 111 1111
po1 22 222 2222
po1 33 333 3333

[唐诗三百首]

select id,[A],[B],[C]
 from (select t.rn,t.id,a.des2,t.v
       from (select rn,id,c,v
             from (select rn=row_number() over(order by getdate()),* from #b) b
             unpivot(v for c in([s1],[s2],[s3])) u) t
       inner join #a a on t.id=a.id and t.c=a.des1) v
 pivot(max(v) for des2 in([A],[B],[C])) p

/*
id         A          B          C
---------- ---------- ---------- ----------
po1        11         111        1111
po1        22         222        2222
po1        33         333        3333

(3 行受影响)
*/

[KnitsEDI]

ABC

引用 1 楼 唐诗三百首 的回复:

select id,[A],[B],[C]
 from (select rn=row_number() over(partition by a.des2 order by getdate()),t.id,a.des2,t.v
       from (select id,c,v
             from #b b
             unpivot(v for c in([s1],[s2],[s3])) u) t
       inner join #a a on t.id=a.id and t.c=a.des1) v
 pivot(max(v) for des2 in([A],[B],[C])) p

/*
id         A          B          C
---------- ---------- ---------- ----------
po1        11         111        1111
po1        22         222        2222
po1        33         333        3333

(3 行受影响)
*/

你好，可能是我没有讲清楚，S1,S2,S3是变化的可能有多行S4,S5,S6........，对应的可能会是ABCDEFG.........
这各情况该 如何处理？

[唐诗三百首]

declare @tsql varchar(max),
        @d varchar(1000),
        @e varchar(1000)
 
select @d=isnull(@d+',','')+'['+des1+']',
       @e=isnull(@e+',','')+'['+des2+']'
 from #a
 
select @tsql='
select id,'+@e+'
 from (select t.rn,t.id,a.des2,t.v
       from (select rn,id,c,v
             from (select rn=row_number() over(order by getdate()),* from #b) b
             unpivot(v for c in('+@d+')) u) t
       inner join #a a on t.id=a.id and t.c=a.des1) v
 pivot(max(v) for des2 in('+@e+')) p '
 
exec(@tsql)

/*
id         A          B          C
---------- ---------- ---------- ----------
po1        11         111        1111
po1        22         222        2222
po1        33         333        3333

(3 行受影响)
*/

[KnitsEDI]

谢 谢大咖！

[KnitsEDI]

引用 3 楼 唐诗三百首 的回复:

declare @tsql varchar(max),
        @d varchar(1000),
        @e varchar(1000)
 
select @d=isnull(@d+',','')+'['+des1+']',
       @e=isnull(@e+',','')+'['+des2+']'
 from #a
 
select @tsql='
select id,'+@e+'
 from (select t.rn,t.id,a.des2,t.v
       from (select rn,id,c,v
             from (select rn=row_number() over(order by getdate()),* from #b) b
             unpivot(v for c in('+@d+')) u) t
       inner join #a a on t.id=a.id and t.c=a.des1) v
 pivot(max(v) for des2 in('+@e+')) p '
 
exec(@tsql)

/*
id         A          B          C
---------- ---------- ---------- ----------
po1        11         111        1111
po1        22         222        2222
po1        33         333        3333

(3 行受影响)
*/

谢谢大咖！