Sqlsever 行转列与列转行

CREATE TABLE [dbo].[Chengji](
    [Name] nvarchar(20) NOT NULL,
    [Kemu] nvarchar(20) NOT NULL,
    [Fenhu] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Chengji] ADD  DEFAULT ((0)) FOR [Fenhu]
GO

INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('张三','语文',80)
GO
INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('张三','数学',60)
GO
INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('张三','英语',99)
GO
INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('李四','语文',80)
GO
INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('李四','数学',80)
GO
INSERT [dbo].[Chengji] ([Name], [Kemu], [Fenhu]) VALUES ('李四','英语',80)
GO

--1.行转列
--方法一:
select name,
sum(case kemu when '语文' then Fenhu else 0 end) '语文',
sum(case kemu when '数学' then Fenhu else 0 end) '数学',
sum(case kemu when '英语' then Fenhu else 0 end) '英语'
from 
Chengji
group by Name
--方法二:
select * from Chengji
pivot (sum(fenhu) for kemu in ([语文],[数学],[英语])) as t

--2.列转行
--创建成绩2表
select *  into Chengji2 from (
select * from Chengji
pivot (sum(fenhu) for kemu in ([语文],[数学],[英语])) as t
) t
--方法一:
select name,'语文' 科目,语文 '成绩'  from Chengji2 union all 
select name,'数学' 科目,数学 '成绩'  from Chengji2  union all 
select name,'英语' 科目,英语 '成绩' from Chengji2
order by name,科目
--方法二:
select * from Chengji2 UNPIVOT([fenshu] for [成绩] in ([语文],[数学],[英语])) as T