问题:如何调用 表S的notext 提取 表N 里的值? 大厂的面试题 有点难度!

 
-- #tb_N简称 表N
DROP TABLE IF EXISTS #tb_N;   --  select * from #tb_N
 
create table #tb_N([ID] [int] IDENTITY(1,1) NOT NULL primary key,Sno varchar(10),n1 INT,n2 INT,n3 INT,n4 INT,n5 INT,n6 INT,n7 INT,n8 INT,n9 INT,n10 INT,n11 INT,n12 INT,n13 INT,n14 INT,n15 INT,n16 INT,n17 INT,n18 INT,n19 INT,n20 INT
) 
insert into #tb_N 
 
SELECT '2021001','9','12','16','18','33','36','40','41','46','48','51','53','54','55','59','61','62','63','68','75'

--- #tb_S简称表S
DROP TABLE IF EXISTS #tb_S;   --  select * from #tb_S
 
create table #tb_S([seq] [int] IDENTITY(1,1) NOT NULL primary key,notext varchar(50)
) 
insert into #tb_S 
SELECT 'n3,n10,n12,n13,n15,n16,n18'


问题:如何调用 表S的notext 提取  表N 里的值?

其结果相当于

SELECT  n3,n10,n12,n13,n15,n16,n18 from  #tb_N; 

想得到的结果是

------------------------------
n3    n10    n12    n13    n15    n16    n18
16    48    53    54    59    61    63

动态SQL？

DECLARE @sql NVARCHAR(max)='';
SELECT @sql = notext FROM #tb_S;

SET @sql = 'select ' + @sql + ' from #tb_N';

EXEC (@sql)

select rn from #tb_N
unpivot (rn for TYPE in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20)) as p

where TYPE in(
select value
from STRING_SPLIT('
select notext from #tb_S

', ',')
)

结果是错误的,如何修正?

不太懂想表达的意思，如果是表s存的是列名，需要更据表s的列动态查表n的数据 可以在程序里动态构造sql查询语句 也可以写sql function 把查询s的结构当作查n的入参