现在有一个数据表,列名依次代表 姓名,科目,分数,现要转换成方便查看的表。
由
N1 SUJ SCO
ZS SX 11
LS SX 98
XY SX 88
ZS YW 99
LS YW 76
XY YW 6
转成
N1 WY SX
LS 76 98
XY 6 88
ZS 99 11
创建表结构 :
CREATE TABLE [dbo].[ZTable_201910121](
[N1] [nvarchar](50) NULL,
[SUJ] [nvarchar](50) NULL,
[SCO] [int] NULL
) ON [PRIMARY]
GO
方法一,使用最土的表嵌套查询:
select N1,
(select t.SCO from [dbo].[ZTable_201910121] t where t.N1=t1.N1 and t.SUJ='YW') AS 'WY',
(select t.SCO from [dbo].[ZTable_201910121] t where t.N1=t1.N1 and t.SUJ='SX') AS 'SX'
FROM [dbo].[ZTable_201910121] t1 group by N1
方法二,使用 CASE WHEN 查询:
SELECT t1.N1,
SUM(CASE WHEN t1.SUJ='SX' THEN t1.SCO ELSE '0' END ) AS 'SX',
SUM(CASE WHEN t1.SUJ='YW' THEN t1.SCO ELSE '0' END ) AS 'YW'
FROM [dbo].[ZTable_201910121] t1 group by N1
方法三,使用 Pivot
select * FROM [dbo].[ZTable_201910121] t
PIVOT (MAX(t.SCO) for t.SUJ in ([SX],[YW])) b
解决