--创建环境
create table myTest(classid int,nd int,inMoney int,outMoney int,saveMoney int)
insert myTest select 1,2005,30,40,50
union select 1,2006,31,41,51
union select 1,2007,32,42,52--初始数据classid nd inMoney outMoney saveMoney
----------- ----------- ----------- ----------- -----------
1 2005 30 40 50
1 2006 31 41 51
1 2007 32 42 52--期望结果:
type 2005 2006 2007
----------------------------
inMoney 30 31 32
outMoney 40 41 42
saveMoney 50 51 52(有点类似于行列转换,谢谢大家了,分不多...保证结贴)
create table myTest(classid int,nd int,inMoney int,outMoney int,saveMoney int)
insert myTest select 1,2005,30,40,50
union select 1,2006,31,41,51
union select 1,2007,32,42,52--初始数据classid nd inMoney outMoney saveMoney
----------- ----------- ----------- ----------- -----------
1 2005 30 40 50
1 2006 31 41 51
1 2007 32 42 52--期望结果:
type 2005 2006 2007
----------------------------
inMoney 30 31 32
outMoney 40 41 42
saveMoney 50 51 52(有点类似于行列转换,谢谢大家了,分不多...保证结贴)
--参考/*
建立日期:2006-12-29
参考网址:http://community.csdn.net/Expert/topic/5259/5259689.xml?temp=.4286768
功能描述:
行列转换(将表旋转90度)
表T:
姓名 语文 数学 物理
----------------------------------
张三 80 90 85
李四 85 92 82要求结果:
课程 张三 李四
----------------------
语文 80 85
数学 90 92
物理 85 82
*/--创建环境
CREATE TABLE T(姓名 nvarchar(10), 语文 int, 数学 int, 物理 int)
INSERT T SELECT '张三', 80, 90, 85
UNION ALL SELECT '李四', 85, 92, 82--SQL
--步骤一:生成中间数据表
DECLARE @sql varchar(8000)
SET @sql ='CREATE TABLE T2(课程 nvarchar(10)'
SELECT @sql=@sql+','+姓名+' nvarchar(10)' FROM T
SET @sql=@sql+')'
EXEC(@sql)
--PRINT @sql
--打印结果:CREATE TABLE T2(课程 nvarchar(10),张三 nvarchar(10),李四 nvarchar(10))--步骤二:借助中间数据表实现行列转换
DECLARE @name nvarchar(10)
DECLARE T_cursor CURSOR FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID('T') and colid>1
ORDER BY colidOPEN T_cursor
FETCH NEXT FROM T_cursor INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('SELECT '+@name+' AS T INTO T3 FROM T')
SET @sql='INSERT INTO T2 SELECT '''+@name+''''
SELECT @sql=@sql+','''+RTRIM(T)+'''' FROM T3
EXEC(@SQL)
EXEC('DROP TABLE T3') FETCH NEXT FROM T_cursor INTO @name
END CLOSE T_cursor
DEALLOCATE T_cursor--查看处理结果
SELECT * FROM T
SELECT * FROM T2--删除环境
DROP TABLE T, T2
go
create table myTest(classid int,nd int,inMoney int,outMoney int,saveMoney int)
insert myTest select 1,2005,30,40,50
union select 1,2006,31,41,51
union select 1,2007,32,42,52 --drop table myTest
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000)
select @s='',@s2='',@s3=''
select @s=@s+','+quotename(nd)+'=sum(case when nd='+rtrim(nd)+' then inMoney else 0 end)',
@s2=@s2+','+quotename(nd)+'=sum(case when nd='+rtrim(nd)+' then outMoney else 0 end)',
@s3=@s3+','+quotename(nd)+'=sum(case when nd='+rtrim(nd)+' then saveMoney else 0 end)'
from
myTest
group by nd
select @s=' select [type]=''inMoney '''+@s+' from myTest group by classid ',
@s2=' select [type]=''outMoney '''+@s2+' from myTest group by classid ',
@s3=' select [type]=''saveMoney '''+@s3+' from myTest group by classid 'exec(@s+' union all '+@s2+' union all '+@s3+' order by [type]')
type 2005 2006 2007
---------- ----------- ----------- -----------
inMoney 30 31 32
outMoney 40 41 42
saveMoney 50 51 52
select [type]='inMoney ',
[2005]=sum(case when nd=2005 then inMoney else 0 end),
[2006]=sum(case when nd=2006 then inMoney else 0 end),
[2007]=sum(case when nd=2007 then inMoney else 0 end)
from myTest group by classid
union all
select [type]='outMoney ',
[2005]=sum(case when nd=2005 then outMoney else 0 end),
[2006]=sum(case when nd=2006 then outMoney else 0 end),
[2007]=sum(case when nd=2007 then outMoney else 0 end)
from myTest group by classid
union all
select [type]='saveMoney ',
[2005]=sum(case when nd=2005 then saveMoney else 0 end),
[2006]=sum(case when nd=2006 then saveMoney else 0 end),
[2007]=sum(case when nd=2007 then saveMoney else 0 end)
from myTest group by classid order by [type]