SQL Server 行列转化 

 1 iF OBJECT_ID('tba') IS NOT NULL DROP TABLE tba
 2 iF OBJECT_ID('tbb') IS NOT NULL DROP TABLE tbb
 3 
 4 go
 5 
 6 CREATE TABLE tba(Code NVARCHAR(55),name NVARCHAR(55))
 7 CREATE TABLE tbb(stuid NVARCHAR(55),code NVARCHAR(55),score NVARCHAR(22))
 8 
 9 
10 INSERT INTO dbo.tba VALUES (1,'数学')
11 INSERT INTO dbo.tba VALUES (2,'英语')
12 INSERT INTO dbo.tba VALUES (3,'语文')
13 INSERT INTO dbo.tba VALUES (4,'历史')
14 INSERT INTO dbo.tbb VALUES ('001',1,90)
15 INSERT INTO dbo.tbb VALUES ('001',3,80)
16 INSERT INTO dbo.tbb VALUES ('002',1,95)
17 INSERT INTO dbo.tbb VALUES ('003',2,85)
18 
19 
20 select * from dbo.tba
21 select * from dbo.tbb
22 
23 DECLARE @tb1 TABLE (stuid VARCHAR(55),[语文] int,[英语]int,[历史]int,[数学]int)
24 INSERT INTO @tb1 ( stuid, 语文, 英语, 历史, 数学 )
25 
26 select stuid,[语文],[英语],[历史],[数学] from 
27 (select a.Name,b.stuid,b.score from dbo.tba a 
28 INNER JOIN dbo.tbb b ON a.Code=b.code) tb PIVOT ( MAX(tb.score) FOR tb.Name IN ([语文],[英语],[历史],[数学])) ff
29 
30 select * from @tb1
31 
32 select * from @tb1 UNPIVOT ( [成绩] FOR [课程] IN ([语文],[英语],[历史],[数学]))a
View Code

 

posted @ 2015-11-12 14:47  wono  阅读(85)  评论(0)    收藏  举报