sqlServer中行转列的小技巧

     之前用过reporting servers的报表矩阵,觉得那个东西比较安逸,不需要行转列,只需要准备好数据源,设置下分组列,直接赋值给矩阵便可.做一个查询界面,需要用到sql的行转列,查了下,摘录整理如下.

创建表

CREATE TABLE [dbo].[Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Class] [varchar](50) NULL,
	[Score] [int] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

生成数据

INSERT INTO [Test]([Name],[Class],[Score]) VALUES ('甲','语文',95)
INSERT INTO [Test]([Name],[Class],[Score]) VALUES ('甲','数学',97)
INSERT INTO [Test]([Name],[Class],[Score]) VALUES ('乙','语文',96)
INSERT INTO [Test]([Name],[Class],[Score]) VALUES ('乙','数学',98)

行列转换

declare @cmdText varchar(8000)
 set @cmdText='select Name, '
 select @cmdText=@cmdText+' sum(case Class when '''+Class+''' Then Score else 0 end) as '''+Class
 +''','+char(10) from (select Distinct Class from Test) T --char(10) 换行符
 set @cmdText=left(@cmdText,len(@cmdText)-2)--去掉逗号
 set @cmdText=@cmdText+'from Test group by Name'
 exec(@cmdText)

完毕!