使用 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
ROW_NUMBER
,我还建议使用交叉表而不是PIVOT
。 - Thom A交叉表
?你能提供一个样例演示吗? - Prashant PimpaleCASE WHEN rn= 1 THEN ApproverName as [1stAppr] ELSE as [2ndAppr]
? - Prashant Pimpale