sql - SQL Server 如何将行转换为列？

Question

使用 MS SQL Server 和我有下表:

+-----------+------------------+------------------+---------------------+-------------------------+
| SrNo      |    ApprCode      |  ApprName        |     ApprStatus      |      ApprDate           |
+-----------+------------------+------------------+---------------------+-------------------------+
| SR_176    |   X001           |   James          |   APR               | 2019-10-03              |
| SR_176    |   X002           |   Sam            |   APR               | 2019-10-03              |
+-----------+------------------+------------------+---------------------+-------------------------+

尝试使用 PIVOT，但它显示 james 和 X001 作为列标题:

预期结果:

+-----------+-------------- +---------------+---------------------+-------------------------+------------------+
    SrNo    | ApprCode_1    | ApprName_1    | ApprDate_2               ApprCode_2     ApprName_2     ApprDate_2  
+-----------+---------------+---------------+---------------------+-------------------------+------------------+
    SR_176     X001             James         2019-10-03               X002           Sam            2019-10-03
+-----------+---------------+---------------+---------------------+---- ---------------------+------------------+

生成数据的查询:

CREATE TABLE #Temp
(
    SrNo NVARCHAR(200),
    ApprCode NVARCHAR(200),
    ApprName NVARCHAR(200),
    ApprDate Date
)

INSERT INTO #Temp VALUES ('SR_176','X001','James', '2019-10-03')
INSERT INTO #Temp VALUES ('SR_176','X002','Sam', '2019-10-03')

我试过的查询:

declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(ApprCode) from #Temp order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(ApprName) from #Temp order by 1 for xml path('')), 1, 1, '')

select @sql = '
;with cte2 as
(
    SELECT SrNo,' + @name_concat + ',' + @name1_concat + '
    FROM #Temp
    PIVOT(MAX(ApprCode)
          FOR ApprCode IN (' + @name_concat + ')) AS PVTTable PIVOT
          (
          MAX(ApprName)
          FOR ApprName IN (' + @name1_concat + ')) AS PVTTable1
)
select * from cte2
'
exec sp_executesql @sql

Answer 1

事实上，这不是SQL Pivot 的情况，问题的示例数据不足以完全测试它，但您可以找到主要思想:

Select
    SrNo,
    MAX(IIF(ApprCode = 'X001', ApprCode, null)) as ApprCode_1,
    MAX(IIF(ApprCode = 'X001', ApprName, null)) as ApprName_1,
    MAX(IIF(ApprCode = 'X001', ApprDate, null)) as ApprDate_1,
    MAX(IIF(ApprCode = 'X002', ApprCode, null)) as ApprCode_2,
    MAX(IIF(ApprCode = 'X002', ApprName, null)) as ApprName_2,
    MAX(IIF(ApprCode = 'X002', ApprDate, null)) as ApprDate_2
From #Temp
Group by SrNo 

如果 ApprCode 是创建两个单独列的键，并且 SrNo 是行之间的分组依据，则此代码有效。