数据表结构
ClassID ClassName starttime endstime1 水电施工 2010-01-23 2010-01-282 木工施工 2010-01-28 2010-01-313 泥工施工 2010-02-1 2010-02-5求以下结果: classid classname date
1 水电施工 2010-01-23
1 水电施工 2010-01-24
1 水电施工 2010-01-25
1 水电施工 2010-01-26
1 水电施工 2010-01-27
1 水电施工 2010-01-28
2 木工施工 2010-01-28
2 木工施工 2010-01-29
2 木工施工 2010-01-30
2 木工施工 2010-01-31
3 泥工施工 2010-02-01
3 泥工施工 2010-02-02
3 泥工施工 2010-02-03
3 泥工施工 2010-02-04
3 泥工施工 2010-02-05
ClassID ClassName starttime endstime1 水电施工 2010-01-23 2010-01-282 木工施工 2010-01-28 2010-01-313 泥工施工 2010-02-1 2010-02-5求以下结果: classid classname date
1 水电施工 2010-01-23
1 水电施工 2010-01-24
1 水电施工 2010-01-25
1 水电施工 2010-01-26
1 水电施工 2010-01-27
1 水电施工 2010-01-28
2 木工施工 2010-01-28
2 木工施工 2010-01-29
2 木工施工 2010-01-30
2 木工施工 2010-01-31
3 泥工施工 2010-02-01
3 泥工施工 2010-02-02
3 泥工施工 2010-02-03
3 泥工施工 2010-02-04
3 泥工施工 2010-02-05
ClassID, ClassName, starttime as date
from
表
union allselect
ClassID, ClassName, endstime as date
from
表最后再将结果按照ClissID排个序就可以了。
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。--------------------
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ClassID] int,[ClassName] varchar(8),[starttime] datetime,[endstime] datetime)
insert [tb]
select 1,'水电施工','2010-01-23','2010-01-28' union all
select 2,'木工施工','2010-01-28','2010-01-31' union all
select 3,'泥工施工','2010-02-1','2010-02-5'select t.[ClassID],t.[ClassName],
dateadd(day,r.number,t.starttime) as [date]
from [tb] t,master..spt_values r
where r.type = 'P' and
datediff(day,dateadd(day,r.number,t.starttime),t.endstime) >= 0
---------------------------
1 水电施工 2010-01-23 00:00:00.000
1 水电施工 2010-01-24 00:00:00.000
1 水电施工 2010-01-25 00:00:00.000
1 水电施工 2010-01-26 00:00:00.000
1 水电施工 2010-01-27 00:00:00.000
1 水电施工 2010-01-28 00:00:00.000
2 木工施工 2010-01-28 00:00:00.000
2 木工施工 2010-01-29 00:00:00.000
2 木工施工 2010-01-30 00:00:00.000
2 木工施工 2010-01-31 00:00:00.000
3 泥工施工 2010-02-01 00:00:00.000
3 泥工施工 2010-02-02 00:00:00.000
3 泥工施工 2010-02-03 00:00:00.000
3 泥工施工 2010-02-04 00:00:00.000
3 泥工施工 2010-02-05 00:00:00.000
我是要把每个时间段的中间的也做为记录
classid classname date
1 水电施工 2010-01-23
1 水电施工 2010-01-24
1 水电施工 2010-01-25
1 水电施工 2010-01-26
1 水电施工 2010-01-27
1 水电施工 2010-01-28