1 /****** Object: Table [dbo].[Table_1] Script Date: 08/06/2013 13:55:39 ******/
2 /*行列转换*/
3
4 SET ANSI_NULLS ON
5 GO
6
7 SET QUOTED_IDENTIFIER ON
8 GO
9
10 SET ANSI_PADDING ON
11 GO
12
13 /*对临时表无效*/
14 IF OBJECT_ID ('#Table_1', 'U') IS NOT NULL
15 DROP TABLE #Table_1;
16
17 CREATE TABLE #Table_1(
18 [name] [varchar](50) NOT NULL,
19 [score] [real] NOT NULL,
20 [subject_id] [int] NOT NULL
21 ) ON [PRIMARY]
22
23 insert into #Table_1 ([name],[score],[subject_id]) values( 'a' , 90 , 1 );
24 insert into #Table_1 ([name],[score],[subject_id]) values( 'b' , 80 , 2 );
25 insert into #Table_1 ([name],[score],[subject_id]) values( 'c' , 70 , 3 );
26 insert into #Table_1 ([name],[score],[subject_id]) values( 'd' , 50 , 1 );
27 insert into #Table_1 ([name],[score],[subject_id]) values( 'e' , 40 , 2 );
28 insert into #Table_1 ([name],[score],[subject_id]) values( 'f' , 60 , 1 );
29
30 --显示1
31
32 SELECT [name],[1],[2],[3]
33 FROM #Table_1
34 pivot
35 (
36 sum(score) for subject_id in ([1],[2],[3])
37 ) as pvt
38
39 select * from #Table_1
40
41 --显示2
42
43 SELECT [name],[subject_id],[score]
44 FROM
45 (
46 SELECT [name],[1],[2],[3]
47 FROM #Table_1
48 pivot
49 (
50 sum(score) for subject_id in ([1],[2],[3])
51 ) as pvt
52 ) p
53 unpivot
54 (
55 score for subject_id in([1],[2],[3])
56 ) as unpvt
