如何在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

- Prashant Pimpale

---

看起来你需要使用 ROW_NUMBER，我还建议使用交叉表而不是 PIVOT。 - Thom A

@Larnu 交叉表？你能提供一个样例演示吗？ - Prashant Pimpale

交叉表和数据透视表，第一部分 - 将行转换为列 - Thom A

@Larnu 我该如何设置 CASE WHEN rn= 1 THEN ApproverName as [1stAppr] ELSE as [2ndAppr]？ - Prashant Pimpale

@Larnu 非常感谢！您可以指导我进行优化或者任何需要提高性能的事情。 - Prashant Pimpale

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用于在行之间进行分组，则此代码有效。

- XAMT

---

正如您所看到的，任何用户都可以作为审批者出现，因此基于审批者代码的方法将无法奏效。 - Prashant Pimpale

Answer 2

我通过使用ROW_NUMBER()和Case Expression来解决它：

以下是查询语句：

 SELECT *,
       Row_number()
         OVER(
           partition BY srno
           ORDER BY apprdate) AS RN
INTO   #temptable
FROM   #temp

SELECT srno,
       CASE rn
         WHEN 1 THEN Max(apprname)
       END AS [1 Approver],
       CASE rn
         WHEN 2 THEN Max(apprname)
       END AS [2 Approver],
       CASE rn
         WHEN 1 THEN Max(apprcode)
       END AS [1 ApproverCode],
       CASE rn
         WHEN 2 THEN Max(apprcode)
       END AS [2 ApproverCode],
       CASE rn
         WHEN 1 THEN Max(apprdate)
       END AS [1 Date],
       CASE rn
         WHEN 2 THEN Max(apprdate)
       END AS [2 Date]
INTO   #james
FROM   #temptable
GROUP  BY srno,
          rn

SELECT srno,
       Max([1 approver])     AS ApproverName_1,
       Max([1 approvercode]) AS ApproverCode_1,
       Max([1 date])         AS ApproverDate_1,
       Max([2 approver])     AS ApproverName_2,
       Max([2 approvercode]) AS ApproverCode_2,
       Max([2 date])         AS ApproverDate_2
FROM   #james
GROUP  BY srno  

- Prashant Pimpale