具有两列的 SQL Server Pivot 动态 Sql

Question

更新 2022-03-04

这是我的 fiddle，显示表格和样本值

创建表代码（
  id int 主键 
  , labVal varchar(50)
  , 标签 varchar(50)
）
插入代码 
价值观
(1,'代码1','重要1')
,(2,'代码2','重要2')
,(3,'代码3','重要3')

创建表客户端（
  id int 主键
  , 工资小数
  , fkCode int 外键引用代码（id）
）
插入客户端 
值 (1,120,3)
,(2,1220,2)
,(3,120, 1)
;

这些是预期结果：

id	important 1	Salary 1	important 2	salary 2	important 3	salary 3	...
1	code1	120	NULL	NULL	NULL	NULL	...
2	NULL	NULL	code2	1220	NULL	NULL	...
3	NULL	NULL	NULL	NULL	code3	120	。 ..

我可以使用下面的查询获取“重要 1,2,3...”的正确数据。但我无法插入“Salary1,2,3,...”的数据，因为数据透视表只允许一系列列。

SELECT * FROM  
(
    SELECT 
    cd.id,
        cd.label, 
        cd.labVal,
        c.salary
     FROM 
        code  cd
  inner join client c on c.fkcode = cd.id 
     
) t 
PIVOT(
    max(labVal) 
    FOR label IN (
        [Important 1]
        ,[salary1]
        ,[Important 2]
        ,[salary2]
        ,[Important 3]
        ,[salary3])
) AS pivot_table;

结果：

<前>id |薪资|重要1 |工资1 |重要2 |工资2 |重要 3 |工资3 -: | -----: | :---------- | :------ | :---------- | :------ | :---------- | :------ 1 | 120 | 120代码1 | 空 | 空 | 空 | 空 | 空 3 | 120 | 120 空 | 空 | 空 | 空 |代码3 | 空 2 | 1220 | 1220 空 | 空 |代码2 | 空 | 空 | 空

---

原始帖子：

我希望能够使用正确的数据生成此示例，

 ------------------------------------------------------------------------------------------------
| id  | whatever | important 1 | custom 1 | important 2 | custom 2 | important 3 | custom 3 |...|
|-----------------------------------------------------------------------------------------------
|x1    |    a    |     NULL    |   NULL   |     code1   |   120      |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     code2   |   450    |     NULL    |   NULL     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     NULL   |   NULL    |     code3    |   250     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------

我能够获得“important1,2,3...”的正确数据，但无法插入自定义数据”由于数据透视表只允许一系列列，因此我无法创建两个数据透视表，因为它们看起来像这样，

---------------------------------------------------------------------------------------------
| id  | whatever | important 1 | important2| important3 | custom 1 | custom 2    |custom 3  |  
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------

“自定义”列是一个临时字段，其中必须包含十进制值，并且每次“重要”列显示时都会重复

。 important”列标题来自一个表，其中包含我转过的名称使用数据透视将行转换为列，并从不同的列分配所需的值。 但自定义列不存在于任何表中，但我想放入其中的值确实存在，问题是，我使用动态 sql 生成带有 row_number 的“自定义”列名称，以避免重复列错误。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);


SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.important) +','+     QUOTENAME( 'custom' + CAST(ROW_NUMBER() OVER (ORDER BY  c.id) as VARCHAR))  as tempfield 
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') 
  
        print @cols


    ----- this @cols
---- will print [important first row to column],[custom1],[important second row to column],[custom2]..


        set @query = 'SELECT  * into #temp from  
            (
                select c.id, c.whatever, c.name, c.customColumnValue  
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
         

           ) x
            pivot 
            (
                 max(name)
                for important in (' + @cols +')
            ) p             select * from #temp

                                  
'

execute(@query)

示例数据位于此处

http://sqlfiddle.com/#!18/ee935/1

Answer 1

我认为您将需要多个 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))< /p>

你有权利那里的想法是，构建一个两列的串联列表，但实际上您需要 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)

结果：

代码 ID |重要1 |薪资1 |重要2 |薪资2 |重要 3 |薪资3
-----: | :---------- | ------: | :---------- | ------: | :---------- | ------:
     1 |代码1 | 120 | 120 空 | 空 | 空 | 空
     2 | 空 | 空 |代码2 | 1220 | 1220 空 | 空
     3 | 空 | 空 | 空 | 空 |代码3 | 120

db<>fiddle 此处