SQL Server透视具有两列的动态SQL

Question

更新2022-03-04

这是我的小提琴，显示的表格和样本值

CREATE TABLE code (
id int primary key 
, labVal varchar(50)
, Label varchar(50)
)
INSERT INTO code 
VALUES
(1,'Code1','Important 1')
,(2,'Code2','Important 2')
,(3,'Code3','Important 3')
CREATE TABLE client (
id int primary key
, Salary decimal
, fkCode int foreign key references code (id)
)
INSERT INTO client 
VALUES (1,120,3)
,(2,1220,2)
,(3,120, 1)
;

这些是预期结果：

薪资1重要2>薪资31220空>

id	重要1	薪资2	重要3
1	code1	120	NULL		NULL		NULL
2	NULL	NULL	代码2	空
	NULL	NULL	空	空		代码3	120

我认为您需要多个PIVOT。的一些事情使它变得稍微复杂一些

• 代码/标签列不是顺序命名为CodeX,CodeY,...
• 薪资列按顺序命名为Salary1,Salary2,...
• SELECT列表列必须分组在一起"CodeX,Salary1,CodeY,Salary2....*

SET@cols=STUFF((SELECT'，'+QUOTENAME(c.important(+'，'+QUOTENAME(('custom'+CAST(ROW_NUMBER((OVER(ORDER BY c.id(as VARCHAR((

您的想法是正确的，构建了两列的串联列表。但实际上您需要3个变量：1(用于代码列2(用于薪资列3(用于选择列表。

注意：必须在所有查询中使用相同的ORDER BY

DECLARE @LabelCols AS VARCHAR(MAX),
@SalaryCols AS VARCHAR(MAX),
@SelectCols AS VARCHAR(MAX),
@Query  AS VARCHAR(MAX);

-- ** SQL Server 2012 doesn't support STRING_AGG()
SET @LabelCols = STUFF((    SELECT  ',' + QUOTENAME(cd.label) 
FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
ORDER BY cd.id
FOR XML PATH('')
),1,1,'') 

SET @SalaryCols = STUFF(( SELECT  ',' + QUOTENAME('Salary'+ 
CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20)) 
) 
FROM code cd INNER JOIN client c ON c.fkcode = cd.id 
FOR XML PATH('')
),1,1,'') 

SET @SelectCols = STUFF(( SELECT  ',' + QUOTENAME(cd.label) 
+ ',' +  QUOTENAME('Salary'+ 
CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20))) 
FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
FOR XML PATH('')
),1,1,'') 

然后在SELECT中使用三个变量：

SET @Query = '
SELECT  ClientId
, CodeId 
, '+ @SelectCols +'
FROM
(
SELECT cd.id AS CodeId
, cd.label
, cd.labVal
, c.salary
, ''Salary''+ CAST(ROW_NUMBER() OVER (ORDER BY  c.id) AS VARCHAR(20)) AS salaryLabel
FROM code cd 
INNER JOIN client c ON c.fkcode = cd.id 
) x
PIVOT
(
MAX(labVal)
FOR label IN (' + @LabelCols +')
) p1
PIVOT
(
MAX(salary)
FOR salaryLabel IN (' + @SalaryCols +')
) p2
'

EXECUTE (@Query)

结果：

CodeId|重要1|薪资1|重要2|薪资2|重要3|薪资3-----：|：---------------|-------：|：------------|--------：|：--------|-------:1|Code1|120|null|null|null2|>null>|null| Code2|1220 |>null|null3|1null|null|2null| Code3|120

db<gt；小提琴这里