PIVOT用于将列值旋转为列名(即行转列),在sql Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法:
table_source
PIVOT(
聚合函数(value_column)
FOR pivot_column
IN(<column_List>)
)
UNPIVOT用于将列明转为列值(即列转行),在sql Server 2000可以用UNION来实现
完整语法:
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_List>)
)
注意:PIVOT、UNPIVOT是sql Server 2005 的语法,使用需修改数据库兼容级别
--------------------------------列转行-------------------------------------- PIVOT 创建测试表,插入测试数据 create table test(ID int,name varchar(20),quarter int,profile int) insert into test values(1,'a',1,1000) insert into test values(1,2,2000) insert into test values(1,3,4000) insert into test values(1,4,5000) insert into test values(2,'b',3000) insert into test values(2,3500) insert into test values(2,4200) insert into test values(2,5500) select * from test ID name quarter profile ------------------------------- 1 a 1 1000 1 a 2 2000 1 a 3 4000 1 a 4 5000 2 b 1 3000 2 b 2 3500 2 b 3 4200 2 b 4 5500 (8 row(s) affected) 利用PIVOT将个季度的利润转成横向显示 select ID,name, [1] as '一季度', [2] as '二季度', [3] as '三季度', [4] as '四季度' from test pivot ( sum(profile) for quarter in ([1],[2],[3],[4]) ) as pvt ID name 一季度 二季度 三季度 四季度 ------------------------------------ 1 a 1000 2000 4000 5000 2 b 3000 3500 4200 5500 (2 row(s) affected) --------------------------------行转列-------------------------------------- UNPIVOT 建立测试表,插入测试数据 drop table test create table test(ID int,Q1 int,Q2 int,Q3 int,Q4 int) insert into test values(1,1000,2000,4000,3000,3500,4200,5500) select * from test ID name Q1 Q2 Q3 Q4 -------------------------------- 1 a 1000 2000 4000 5000 2 b 3000 3500 4200 5500 (2 row(s) affected) 利用UNPIVOT,将同一行中四个季度的列数据转换成四行数据 select ID,quarter,profile from test unpivot ( profile for quarter in ([Q1],[Q2],[Q3],[Q4]) ) as unpvt ID name quarter profile ------------------------------- 1 a Q1 1000 1 a Q2 2000 1 a Q3 4000 1 a Q4 5000 2 b Q1 3000 2 b Q2 3500 2 b Q3 4200 2 b Q4 5500 (8 row(s) affected) --------------------------------列转行-------------------------------------- DROP table #student CREATE table #student (stdname nvarchar(10),stdsubject nvarchar(10),result int) INSERT INTO #student VALUES ('张三','语文',80) INSERT INTO #student values ('张三','数学',90) INSERT INTO #student VALUES ('张三','物理',85) INSERT INTO #student VALUES ('李四',85) INSERT INTO #student values ('李四',92) INSERT INTO #student VALUES ('李四',82) INSERT INTO #student VALUES ('李四','化学',82) SELECT * FROM #student 建立临时表,对数据进行处理(静态的sql 1) select distinct * into #table from #student select * from #table select stdname,isnull(sum( case stdsubject when '语文' then Result end),0 ) [语文], isnull(sum( case stdsubject when '数学' then Result end),0 ) [数学],isnull(sum( case stdsubject when '物理' then Result end),0 ) [物理],isnull(sum( case stdsubject when '化学' then Result end),0 ) [化学] from #table group by stdname drop table #table (静态的sql 2) select distinct * into #table from #student select stdname, [语文] as "语文", [数学] as "数学", [物理] as "物理", [化学] as "化学" into #table2 from #table pivot(sum(result) for stdsubject in ([语文],[数学],[物理],[化学])) as pvt select stdname,语文,isnull(数学,0) '数学',物理,isnull(化学,0) '化学' from #table2 drop table #table drop table #table2 (动态sql 1) declare @sql varchar(1000) select distinct * into #table from #student set @sql = 'select stdname' select @sql = @sql + ',isnull(sum(case stdsubject when '''+ stdsubject +''' then Result end),0) [ ' + stdsubject + ' ] ' from (select distinct stdsubject from #table) as rs select @sql = @sql + ' from #table group by stdname' print @sql exec (@sql) drop table #table (动态sql 2),不用临时表 declare @sql varchar(1000) set @sql = 'select stdname' select @sql = @sql + ',isnull(sum(distinct case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] ' from (select distinct stdsubject from #student) as rs select @sql = @sql + ' from #student group by stdname' print @sql exec (@sql) --------------------------------行转列-------------------------------------- DROP table #student2 CREATE table #student2 (stdname nvarchar(10),化学 int,数学 int,物理 int,语文 int ) INSERT INTO #student2 VALUES ('李四',164,92,82,85) INSERT INTO #student2 VALUES ('张三',90,85,80) SELECT * FROM #student2 select [name] into #tmpCloumns from tempdb.dbo.syscolumns where ID=object_ID('tempdb.dbo.#student2') and [name]<>'stdname' select * from #tmpCloumns declare @strsql nvarchar(800) select @strsql='' select @strsql=@strsql+'union all'+char(10)+char(13)+'select [stdname],'''+[name]+''' as [科目],['+[name]+']'+char(10)+char(13)+'from [#student2]'+char(10)+char(13) from #tmpCloumns select @strsql=substring(@strsql,11,len(@strsql))+'order by stdname,[科目]' --print @strsql exec(@strsql)
总结以上是内存溢出为你收集整理的SQLServer行列转换 Pivot UnPivot全部内容,希望文章能够帮你解决SQLServer行列转换 Pivot UnPivot所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)