flight effectdate subclass
------- ------------------------------------------------------ --------
va1235 2007-04-18 00:00:00.000 a
va1235 2007-04-18 00:00:00.000 d
va1235 2007-04-18 00:00:00.000 f
va1235 2007-04-18 00:00:00.000 e
va1235 2007-04-18 00:00:00.000 v
va1235 2007-04-19 00:00:00.000 c
va1235 2007-04-19 00:00:00.000 d
va1235 2007-04-19 00:00:00.000 f
.......
对同一flight进行按日期的归并,如下转成
flight effectdate a d f e v c
va1235 2007-4-18 T T T T T NULL
va1235 2007-4-19 null T T null null T
.....
------- ------------------------------------------------------ --------
va1235 2007-04-18 00:00:00.000 a
va1235 2007-04-18 00:00:00.000 d
va1235 2007-04-18 00:00:00.000 f
va1235 2007-04-18 00:00:00.000 e
va1235 2007-04-18 00:00:00.000 v
va1235 2007-04-19 00:00:00.000 c
va1235 2007-04-19 00:00:00.000 d
va1235 2007-04-19 00:00:00.000 f
.......
对同一flight进行按日期的归并,如下转成
flight effectdate a d f e v c
va1235 2007-4-18 T T T T T NULL
va1235 2007-4-19 null T T null null T
.....
解决方案 »
- 帮忙设计一个er图
- 各位达人进来帮帮忙,一条SQL语句
- 用sql建立了一个local数据库以后,里面的表有很多是系统自行定义的,能不能删除啊?
- SQL问题?(在线)
- 各位老师,请问关于流水号的sql怎么写?
- 为何不能远程连接sqlserver数据库?
- 在sql2005中创建全文目录失败?
- 请教高手,这样的要求如何实现
- 利用存储过程怎样把一个记录集中的数据成批的最加到另一个表中?(在线等待)
- 在SQL数据库开发过程中,怎样利用中间表存放结果?
- 有一个Access数据库和一个Sql数据库,两个库的结构完全一样,但是内容不一样,怎么写SQL语句,把Access中的内容不在SQL中的导入到SQL中?
- 请教关于sqlserver2000触发器delete的问题
effectdate,
max(case when subclass='a' then 'T' end) as a,
max(case when subclass='d' then 'T' end) as d,
max(case when subclass='f' then 'T' end) as f,
max(case when subclass='e' then 'T' end) as e,
max(case when subclass='v' then 'T' end) as v,
max(case when subclass='c' then 'T' end) as c
from tbtest
group by flight,effectdate
Select @S = 'Select flight, Convert(Varcahr(10), effectdate, 120) As effectdate'
Select @S = @S + ', Max(Case subclass When ''' + subclass + ''' Then T Else Null End) As [' + subclass + ']' From TableName Group By subclass
Select @S = @S+ ' From TableName Group By flight, Convert(Varcahr(10), effectdate, 120)'
EXEC(@S)
drop table tbtest
go
create table tbtest(flight varchar(10),effectdate datetime,subclass char(1))
insert into tbtest
select 'va1235','2007-04-18 00:00:00.000','a'
union all select 'va1235','2007-04-18 00:00:00.000','d'
union all select 'va1235','2007-04-18 00:00:00.000','f'
union all select 'va1235','2007-04-18 00:00:00.000','e'
union all select 'va1235','2007-04-18 00:00:00.000','v'
union all select 'va1235','2007-04-19 00:00:00.000','c'
union all select 'va1235','2007-04-19 00:00:00.000','d'
union all select 'va1235','2007-04-19 00:00:00.000','f'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when subclass='''+subclass+''' then ''t'' end) as '''+subclass+''''
from (select distinct subclass from tbtest)t
exec('select flight,effectdate'+@sql+' from tbtest group by flight,effectdate')
Declare @S Varchar(8000)
Select @S = 'Select flight, Convert(Varchar(10), effectdate, 120) As effectdate'
Select @S = @S + ', Max(Case subclass When ''' + subclass + ''' Then ''T'' Else Null End) As [' + subclass + ']' From TEST Group By subclass
Select @S = @S+ ' From TEST Group By flight, Convert(Varchar(10), effectdate, 120)'
EXEC(@S)
(flight Varchar(10),
effectdate DateTime,
subclass Varchar(10))
Insert TEST Select 'va1235', '2007-04-18 00:00:00.000', 'a'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'd'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'f'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'e'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'v'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'c'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'd'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'f'
GO
--如果subclass固定
Select
flight,
Convert(Varchar(10), effectdate, 120) As effectdate,
Max(Case subclass When 'a' Then 'T' Else Null End) As a,
Max(Case subclass When 'c' Then 'T' Else Null End) As c,
Max(Case subclass When 'd' Then 'T' Else Null End) As d,
Max(Case subclass When 'e' Then 'T' Else Null End) As e,
Max(Case subclass When 'f' Then 'T' Else Null End) As f,
Max(Case subclass When 'v' Then 'T' Else Null End) As v
From
TEST
Group By
flight, Convert(Varchar(10), effectdate, 120)--如果subclass不固定
Declare @S Varchar(8000)
Select @S = 'Select flight, Convert(Varchar(10), effectdate, 120) As effectdate'
Select @S = @S + ', Max(Case subclass When ''' + subclass + ''' Then ''T'' Else Null End) As [' + subclass + ']' From TEST Group By subclass
Select @S = @S+ ' From TEST Group By flight, Convert(Varchar(10), effectdate, 120)'
EXEC(@S)
GO
Drop Table TEST
--Result
/*
flight effectdate a c d e f v
va1235 2007-04-18 T NULL T T T T
va1235 2007-04-19 NULL T T NULL T NULL
*/
create table ta (flight char(6), effectdate datetime, subclass varchar(2))
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'a'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'd'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'f'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'e'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'v'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'c'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'd'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'f'
select *,id=1 into #
from ta order by flightdeclare @s varchar(10),@i int
update #
set @i=
case when convert(varchar(10),effectdate,120)=@s then @i+1 else 1 end,
@s=convert(varchar(10),effectdate,120),id=@i
--执行
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+',[记录'+rtrim(id)+']=max(case when id='+rtrim(id)+' then subclass end)'
from # group by id
--print @sql
exec('select flight,effectdate=convert(varchar(10),effectdate,120)'+@sql
+' from # group by flight,convert(varchar(10),effectdate,120)')
flight effectdate 记录1 记录2 记录3 记录4 记录5
------ ---------- ---- ---- ---- ---- ----
va1235 2007-04-18 a d f e v
va1235 2007-04-19 c d f NULL NULL
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'a'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'd'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'f'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'e'
insert ta select 'va1235' ,'2007-04-18 00:00:00.000', 'v'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'c'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'd'
insert ta select 'va1235' ,'2007-04-19 00:00:00.000', 'f'
--执行
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+','+quotename(subclass)+'=max(case when subclass='+quotename(subclass,'''')+' then ''T'' end)'
from ta group by subclass
--print @sql
exec('select flight,coeffectdate=convert(varchar(10),effectdate,120)'+@sql+
' from ta group by flight,convert(varchar(10),effectdate,120)')flight coeffectdate a c d e f v
------ ------------ ---- ---- ---- ---- ---- ----
va1235 2007-04-18 T NULL T T T T
va1235 2007-04-19 NULL T T NULL T NULL
(2 行受影响)
Create Table TEST
(flight Varchar(10),
effectdate DateTime,
subclass Varchar(10))
Insert TEST Select 'va1235', '2007-04-18 00:00:00.000', 'a'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'd'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'f'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'e'
Union All Select 'va1235', '2007-04-18 00:00:00.000', 'v'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'c'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'd'
Union All Select 'va1235', '2007-04-19 00:00:00.000', 'f'
GO
--如果subclass固定
Declare @flight Varchar(10)
Select @flight = 'va1235'
Select
flight,
Convert(Varchar(10), effectdate, 120) As effectdate,
Max(Case subclass When 'a' Then 'T' Else Null End) As a,
Max(Case subclass When 'c' Then 'T' Else Null End) As c,
Max(Case subclass When 'd' Then 'T' Else Null End) As d,
Max(Case subclass When 'e' Then 'T' Else Null End) As e,
Max(Case subclass When 'f' Then 'T' Else Null End) As f,
Max(Case subclass When 'v' Then 'T' Else Null End) As v
From
TEST
Where flight = @flight --這裡加入條件
Group By
flight, Convert(Varchar(10), effectdate, 120)--如果subclass不固定
Declare @S Varchar(8000)
Select @S = 'Select flight, Convert(Varchar(10), effectdate, 120) As effectdate'
Select @S = @S + ', Max(Case subclass When ''' + subclass + ''' Then ''T'' Else Null End) As [' + subclass + ']' From TEST Where flight = @flight Group By subclass --這裡加入條件
Select @S = @S+ ' From TEST Where flight = ''' + @flight + ''' Group By flight, Convert(Varchar(10), effectdate, 120)' --這裡加入條件
EXEC(@S)
GO
Drop Table TEST
--Result
/*
flight effectdate a c d e f v
va1235 2007-04-18 T NULL T T T T
va1235 2007-04-19 NULL T T NULL T NULL
*/