刚刚学的:
请参考,绝对可以实现declare @a table(时间 datetime,地名 nvarchar(20),数据 int)
insert @a(时间,地名,数据)
values('1990-1-1','北京',85)
insert @a(时间,地名,数据)
values('1990-1-2','北京',92)
insert @a(时间,地名,数据)
values('1990-1-1','天津',52)
insert @a(时间,地名,数据)
values('1990-1-2','天津',98)
select * from @aselect 时间,北京=(select 数据 from @a where 地名='北京' and 时间=a.时间),
天津=(select 数据 from @a where 地名='天津' and 时间=a.时间) from @a a group by 时间
请参考,绝对可以实现declare @a table(时间 datetime,地名 nvarchar(20),数据 int)
insert @a(时间,地名,数据)
values('1990-1-1','北京',85)
insert @a(时间,地名,数据)
values('1990-1-2','北京',92)
insert @a(时间,地名,数据)
values('1990-1-1','天津',52)
insert @a(时间,地名,数据)
values('1990-1-2','天津',98)
select * from @aselect 时间,北京=(select 数据 from @a where 地名='北京' and 时间=a.时间),
天津=(select 数据 from @a where 地名='天津' and 时间=a.时间) from @a a group by 时间
from 表 a group by bh
insert into 表
select 1,'2005-02-01','A' union all
select 1,'2005-02-02', 'A' union all
select 1,'2005-02-03', 'B' union all
select 2,'2005-02-01', 'B' union all
select 2,'2005-02-02', 'A'
----------------------------------------------------
select * from 表
-------表的內容-----------
bh rq data
---- ------------------------------- -------
1 2005-02-01 00:00:00.000 A
1 2005-02-02 00:00:00.000 A
1 2005-02-03 00:00:00.000 B
2 2005-02-01 00:00:00.000 B
2 2005-02-02 00:00:00.000 A
---------------處理語句--------------------------------
select bh,[01]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='01' ),''),
[02]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='02' ),'') ,
[03]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='03' ),'')
from 表 a group by bh
--------------------處理後結果-----------------------
bh 01 02 03
1 A A B
2 B A
(bh int,rq varchar(10),data varchar(1))
insert into tb
select 1 , '2005-02-01', 'A' union all
select 1 , '2005-02-02', 'A' union all
select 1 , '2005-02-03', 'B'
declare @s nvarchar(4000)
set @s='select bh'
select @s=@s+',['+right(rq,2)+']= max(case when right(rq,2)='''+right(rq,2)+''' then data else space(1) end) '
from (select distinct right(rq,2) as rq from tb) a
select @s=@s+' from tb group by bh'
exec(@s)
select 2 , '2005-02-01', 'B' union all
select 2 , '2005-02-02', 'A'declare @s nvarchar(4000)
set @s='select bh'
select @s=@s+',['+right(rq,2)+']= max(case when right(rq,2)='''+right(rq,2)+''' then data else space(1) end) '
from (select distinct right(rq,2) as rq from tb) a
select @s=@s+' from tb group by bh'
exec(@s)
bh 01 02 03
----------- ---- ---- ----
1 A A B
2 B A
(所影响的行数为 2 行)
Create table 表(bh int, rq datetime, data varchar(1))
insert into 表
select 1,'2005-02-01','A' union all
select 1,'2005-02-02', 'A' union all
select 1,'2005-02-03', 'B' union all
select 2,'2005-02-01', 'B' union all
select 2,'2005-02-02', 'A' union all
select 3,'2005-03-02','C' union all
select 2,'2005-03-02','D'
----------------------------------------------------
---------------處理語句--------------------------------
select bh,[01]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='01' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),''),
[02]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='02' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),'') ,
[03]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='03' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7)),'')
from 表 a group by bh,left(convert(varchar(10),rq,120),7)
--------------------處理後結果-----------------------
1 A A B
2 B A
2 D
3 C
01 02 03 ...
1 A A B ...
2 B A ...
的栏位修改为:
显示成:
01 02 03 ...
1 B B A ...
2 B A ...怎样更新表:bh rq data
1 2005-02-01 B
1 2005-02-02 B
1 2005-02-03 A
....
2 2005-02-01 B
2 2005-02-02 A
....
?
Create table 表(bh int, rq datetime, data varchar(1))
insert into 表
select 1,'2005-02-01','A' union all
select 1,'2005-02-02', 'A' union all
select 1,'2005-02-03', 'B' union all
select 2,'2005-02-01', 'B' union all
select 2,'2005-02-02', 'A' union all
select 3,'2005-03-02','C' union all
select 2,'2005-03-02','D'
----------------------------------------------------
---------------處理語句--------------------------------
select bh,[01]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='01' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),''),
[02]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='02' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),'') ,
[03]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='03' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7)),''),
month=left(convert(varchar(10),rq,120),7)----這裡加月份區別 ,如果不要就可刪除這段
from 表 a group by bh,left(convert(varchar(10),rq,120),7)
--------------------處理後結果-----------------------
1 A A B
2 B A
2 D
3 C
Create table 表(bh int, rq datetime, data varchar(1))
insert into 表
select 1,'2005-02-01','A' union all
select 1,'2005-02-02', 'A' union all
select 1,'2005-02-03', 'B' union all
select 2,'2005-02-01', 'B' union all
select 2,'2005-02-02', 'A' union all
select 3,'2005-03-02','C' union all
select 2,'2005-03-02','D'
----------------------------------------------------
---------------處理語句--------------------------------
select bh,[01]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='01' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),''),
[02]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='02' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7) ),'') ,
[03]=isnull((select data from 表 where bh=a.bh and right(convert(varchar(10),rq,120),2)='03' and left(convert(varchar(10),rq,120),7)=left(convert(varchar(10),a.rq,120),7)),''),
month=left(convert(varchar(10),rq,120),7)
from 表 a group by bh,left(convert(varchar(10),rq,120),7)
--------------------處理後結果-----------------------
1 A A B 2005-02
2 B A 2005-02
2 D 2005-03
3 C 2005-03
01 02 03 ...
1 A A B ...
2 B A ...
的栏位修改为:
显示成:
01 02 03 ...
1 B B A ...
2 B A ...
-------------樓主那你的上面這個必髯是一個表才行呀,否則改呢