关于横变竖的问题
数据如下：
class name 年份 1月 2月 3月 ……
一班 张三 2007 5元 2元 5元
一班 李四 2006 3元 0元 1元
二班 王五 2007 0元 0元 1元
……
要求输出的数据如下：
一班 张三 2007 1月 5元
一班 张三 2007 2月 2元
一班 张三 2007 3月 5元
一班 李四 2006 1月 3元
一班 李四 2006 3月 1元
二班 王五 2007 3月 1元
……
就是将“月份”由横变竖，请大家帮帮忙，写一下储存过程，谢谢大家了，高分相送，要求执行效率最高的。
[解决办法]
普通行列转换
假设有张学生成绩表(t)如下
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94
create table #t
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into #t(Name , Subject , Result) values( '张三 ', '语文 ', '73 ')
insert into #t(Name , Subject , Result) values( '张三 ', '数学 ', '83 ')
insert into #t(Name , Subject , Result) values( '张三 ', '物理 ', '93 ')
insert into #t(Name , Subject , Result) values( '李四 ', '语文 ', '74 ')
insert into #t(Name , Subject , Result) values( '李四 ', '数学 ', '83 ')
insert into #t(Name , Subject , Result) values( '李四 ', '物理 ', '93 ')
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名 '
select @sql = @sql + ' , sum(case Subject when ' ' ' + Subject + ' ' ' then Result end) [ ' + Subject + '] '
from (select distinct Subject from #t) as a
set @sql = @sql + ' from #t group by name '
exec(@sql)
drop table #t
--结果
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 83 93 74
张三 83 93 73
----------------------------------------------------
如果上述两表互相换一下：即
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94
想变成
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
create table #t
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into #t(姓名 , 语文 , 数学 , 物理) values( '张三 ',73,83,93)
insert into #t(姓名 , 语文 , 数学 , 物理) values( '李四 ',74,84,94)
select 姓名 as Name, '语文 ' as Subject,语文 as Result from #t union
select 姓名 as Name, '数学 ' as Subject,数学 as Result from #t union
select 姓名 as Name, '物理 ' as Subject,物理 as Result from #t
order by 姓名 desc
drop table #t
--结果
Name Subject Result
---------- ------- -----------
张三 数学 83
张三 物理 93
张三 语文 73
李四 数学 84
李四 物理 94
李四 语文 74
(所影响的行数为 6 行)
[解决办法]
select a.* from(
select class,name,年份, '1月 ' 月份,1月 from tb
union all
select class,name,年份, '2月 ' 月份,2月 from tb
union all
...
)a
[解决办法]
declare @t table(class varchar(10),name varchar(10),年份 varchar(10),[1月] varchar(10),[2月] varchar(10),[3月] varchar(10))
insert into @t select '一班 ', '张三 ', '2007 ', '5元 ', '2元 ', '5元 '
union all select '一班 ', '李四 ', '2006 ', '3元 ', '0元 ', '1元 '
union all select '二班 ', '王五 ', '2007 ', '0元 ', '0元 ', '1元 '
select * from @t
unpivot
( 金额 for 月份 in ([1月],[2月],[3月])
) unpt
where 金额 <> '0元 '
/*
class name 年份 金额 月份
---------- ---------- ---------- ---------- -----------
一班 张三 2007 5元 1月
一班 张三 2007 2元 2月
一班 张三 2007 5元 3月
一班 李四 2006 3元 1月
一班 李四 2006 1元 3月
二班 王五 2007 1元 3月
(6 行受影响)
*/
[解决办法]
万一这个 表是 动态 生成的呢
就算是只有12个月
也可能是 3个月 6个月 8 个月....
[解决办法]
--MS SQL2000下月份不固定的動態寫法
Create Table TEST
(classNvarchar(10),
nameNvarchar(10),
年份Int,
[1月]Varchar(10),
[2月]Varchar(10),
[3月]Varchar(10))
Insert TEST Select N '一班 ',N '张三 ',2007, '5元 ', '2元 ', '5元 '
Union All Select N '一班 ',N '李四 ',2006, '3元 ', '0元 ', '1元 '
Union All Select N '二班 ',N '王五 ',2007, '0元 ', '0元 ', '1元 '
GO
Declare @S Nvarchar(4000)
Select @S = ' '
Select @S = @S + ' Union Select class, name, 年份, ' ' ' + Name + ' ' ' As 月份, [ ' + Name + '] As 元 From TEST '
From SysColumns Where ID = OBJECT_ID( 'TEST ') And Name Like '%月 ' Order By Name
Select @S = Stuff(@S, 1, 7, ' ')
Print @S
EXEC(@S)
GO
Drop Table TEST
--Result
/*
classname年份月份元
一班李四20061月3元
一班李四20062月0元
一班李四20063月1元
一班张三20071月5元
一班张三20072月2元
一班张三20073月5元
二班王五20071月0元
二班王五20072月0元
二班王五20073月1元
*/
[解决办法]
只有用鱼的字符拼揍的方法实现:
select class , name, 年份,1月
from 表名
union all
select class , name, 年份,2月
union all
...........
[解决办法]
--或者這麼修改也可以
--MS SQL2000下月份不固定的動態寫法
Create Table TEST
(classNvarchar(10),
nameNvarchar(10),
年份Int,
[1月]Numeric(10, 2),
[2月]Numeric(10, 2),
[3月]Numeric(10, 2))
Insert TEST Select N '一班 ',N '张三 ',2007,5,2,5
Union All Select N '一班 ',N '李四 ',2006,3,0,1
Union All Select N '二班 ',N '王五 ',2007,0,0,1
GO
Declare @S Nvarchar(4000)
Select @S = ' '
Select @S = @S + ' Union Select class, name, 年份, ' ' ' + Name + ' ' ' As 月份, [ ' + Name + '] As 元 From TEST Where [ ' + Name + '] != 0 '
From SysColumns Where ID = OBJECT_ID( 'TEST ') And Name Like '%月 ' Order By Name
Select @S = Stuff(@S, 1, 7, ' ')
Print @S
EXEC(@S)
GO
Drop Table TEST
--Result
/*
classname年份月份元
一班李四20061月3.00
一班李四20063月1.00
一班张三20071月5.00
一班张三20072月2.00
一班张三20073月5.00
二班王五20073月1.00
*/
[解决办法]
CREATE TABLE #A
(CLASS varchar(20),
[NAME] varchar(20),
年份 varchar(20),
一月 int,
二月 int,
三月 int
)
INSERT INTO #A
SELECT '一班 ', '张三 ', '2007 ', '5 ', '2 ', '5 '
UNION ALL
SELECT '一班 ', '李四 ', '2006 ', '3 ', '0 ', '1 '
UNION ALL
SELECT '二班 ', '王五 ', '2007 ', '0 ', '0 ', '1 '
SELECT * FROM
(
SELECT NAME,年份,月份= '1月 ',一月
FROM #A
WHERE 一月 <> '0 '
UNION ALL
SELECT NAME,年份,月份= '2月 ',二月
FROM #A
WHERE 二月 <> '0 '
UNION ALL
SELECT NAME,年份,月份= '3月 ',三月
FROM #A
WHERE 三月 <> '0 '
) A
ORDER BY A.NAME
搞笑写法，不过能实现要求！：）
