卡号 时间
80005 2009-2-21
80030 2009-2-21
80030 2009-4-19
80049 2009-2-21
80051 2009-2-14
80051 2009-3-21
80124 2009-5-16
80180 2009-4-11
80180 2009-4-18
80180 2009-4-25
转成
卡号 时间 卡号 时间 卡号 时间 卡号 时间
80341 2009-3-14 80341 2009-3-14 80341 2009-2-14 80341 2009-2-21
80349 2009-3-7
卡号不固定,
在线等,帮帮忙吧,给个思路也行。
80005 2009-2-21
80030 2009-2-21
80030 2009-4-19
80049 2009-2-21
80051 2009-2-14
80051 2009-3-21
80124 2009-5-16
80180 2009-4-11
80180 2009-4-18
80180 2009-4-25
转成
卡号 时间 卡号 时间 卡号 时间 卡号 时间
80341 2009-3-14 80341 2009-3-14 80341 2009-2-14 80341 2009-2-21
80349 2009-3-7
卡号不固定,
在线等,帮帮忙吧,给个思路也行。
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(卡号 int, 时间 datetime )
go
insert tb SELECT
80005, '2009-2-21' UNION ALL SELECT
80030, '2009-2-21' UNION ALL SELECT
80030, '2009-4-19' UNION ALL SELECT
80049, '2009-2-21' UNION ALL SELECT
80051, '2009-2-14' UNION ALL SELECT
80051, '2009-3-21' UNION ALL SELECT
80124, '2009-5-16' UNION ALL SELECT
80180, '2009-4-11' UNION ALL SELECT
80180, '2009-4-18' UNION ALL SELECT
80180, '2009-4-25'
go
select
卡号1=max(case when rn%8=1 then rtrim(卡号) else '' end) ,
时间1=MAX(case when rn%8=2 then CONVERT(varchar(10),时间,120) else '' end),
卡号2=max(case when rn%8=3 then rtrim(卡号) else '' end) ,
时间2=MAX(case when rn%8=4 then CONVERT(varchar(10),时间,120) else '' end),
卡号3=max(case when rn%8=5 then rtrim(卡号) else '' end) ,
时间3=MAX(case when rn%8=6 then CONVERT(varchar(10),时间,120) else '' end),
卡号4=max(case when rn%8=7 then rtrim(卡号) else '' end) ,
时间4=MAX(case when rn%8=0 then CONVERT(varchar(10),时间,120) else '' end)
from (select rn=ROW_NUMBER() over (order by 卡号 ),*from tb ) k
group by rn/9卡号1 时间1 卡号2 时间2 卡号3 时间3 卡号4 时间4
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-02-21 80051 2009-03-21 80124 2009-04-11
80180 2009-04-25 (2 行受影响)
但是数据量很多,卡号可能重复十几个,有没有动态的解决方法
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb-->SQL查询如下:
alter table tb add id int identity
goselect
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from tb
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-02-21 80030 2009-04-19 80049 2009-02-21
80051 2009-02-14 80051 2009-03-21 80124 2009-05-16 80180 2009-04-11
80180 2009-04-18 80180 2009-04-25 (3 行受影响)
*/
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb-->SQL查询如下:
select id=identity(int),* into # from tb t where not exists(select 1 from tb where 卡号=t.卡号 and 时间>t.时间)select
max(case (id-1)%4 when 0 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 0 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 1 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 1 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 2 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 2 then convert(varchar(10),时间,23) else '' end) [时间],
max(case (id-1)%4 when 3 then ltrim(卡号) else '' end) [卡号],
max(case (id-1)%4 when 3 then convert(varchar(10),时间,23) else '' end) [时间]
from #
group by (id-1)/4
/*
卡号 时间 卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21 80030 2009-04-19 80049 2009-02-21 80051 2009-03-21
80124 2009-05-16 80180 2009-04-25 (2 行受影响)
*/
drop table #
TONY哥。。
楼主的数据是错误的
他的需求是要
凡是相同的卡号,所有数据都放在一行。、
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([卡号] int,[时间] datetime)
insert [tb]
select 80005,'2009-2-21' union all
select 80030,'2009-2-21' union all
select 80030,'2009-4-19' union all
select 80049,'2009-2-21' union all
select 80051,'2009-2-14' union all
select 80051,'2009-3-21' union all
select 80124,'2009-5-16' union all
select 80180,'2009-4-11' union all
select 80180,'2009-4-18' union all
select 80180,'2009-4-25'
---查询---
declare @sql varchar(8000),@i int,@maxcnt int
select @i=1,@maxcnt=max(cnt) from (select 卡号,count(1) as cnt from tb group by 卡号) t
while @i<=@maxcnt
begin
select @sql=isnull(@sql+',','')+'卡号,时间=max(case px when '+rtrim(@i)+' then convert(varchar(10),时间,120) else '''' end)',@i=@i+1
end
set @sql= 'select '
+@sql
+' from (select *,px=(select count(1)+1 from tb where 卡号=t.卡号 and 时间>t.时间) from tb t) tt group by 卡号' exec(@sql) ---结果---
卡号 时间 卡号 时间 卡号 时间
----------- ---------- ----------- ---------- ----------- ----------
80005 2009-02-21 80005 80005
80030 2009-04-19 80030 2009-02-21 80030
80049 2009-02-21 80049 80049
80051 2009-03-21 80051 2009-02-14 80051
80124 2009-05-16 80124 80124
80180 2009-04-25 80180 2009-04-18 80180 2009-04-11
select cord,min(id) id into tt from t1 group by cord ) as tt
where t1.id=tt.id
select cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--2
select t1.cord,ntime,address,ls,[className], [days], [order] into tb2 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--3
select t1.cord,ntime,address,ls,[className], [days], [order] into tb3 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--4
select t1.cord,ntime,address,ls,[className], [days], [order] into tb4 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--5select t1.cord,ntime,address,ls,[className], [days], [order] into tb5 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt--6select t1.cord,ntime,address,ls,[className], [days], [order] into tb6 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt--7select t1.cord,ntime,address,ls,[className], [days], [order] into tb7 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--8select t1.cord,ntime,address,ls,[className], [days], [order] into tb8 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--9select t1.cord,ntime,address,ls,[className], [days], [order] into tb9 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt--10select t1.cord,ntime,address,ls,[className], [days], [order] into tb10 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table tt
--11select t1.cord,ntime,address,ls,[className], [days], [order] into tb11 from t1,(
select cord,min(id) id from t1 group by cord ) as tt
where t1.id=tt.idselect cord,min(id) id into tt from t1 group by cord
delete t1 from tt where t1.cord=tt.cord and t1.id=tt.id
drop table ttselect * from tb1
left join tb2 on tb1.cord=tb2.cord
left join tb3 on tb1.cord=tb3.cord
left join tb4 on tb1.cord=tb4.cord
left join tb5 on tb1.cord=tb5.cord
left join tb6 on tb1.cord=tb6.cord
left join tb7 on tb1.cord=tb7.cord
left join tb8 on tb1.cord=tb8.cord
left join tb9 on tb1.cord=tb9.cord
left join tb10 on tb1.cord=tb10.cord
left join tb11 on tb1.cord=tb11.cord
alter table t1 add id int identity
Drop table [tb]
Go
Create table [tb]([卡号] int,[时间] Datetime)
Insert tb
Select 80005,'2009-2-21' union all
Select 80030,'2009-2-21' union all
Select 80030,'2009-4-19' union all
Select 80049,'2009-2-21' union all
Select 80051,'2009-2-14' union all
Select 80051,'2009-3-21' union all
Select 80124,'2009-5-16' union all
Select 80180,'2009-4-11' union all
Select 80180,'2009-4-18' union all
Select 80180,'2009-4-25'
Go
--Select * from tb
--添加一列
alter table tb add id int identity
go
--
declare @s varchar(8000)
select @s=isnull(@s+',','select ')+'
卡号=max(case px when '+ltrim(px)+' then ltrim(卡号) else '''' end),
时间=max(case px when '+ltrim(px)+' then convert(varchar(10),时间,23) else '''' end)'
from (select distinct px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id) from tb t) t
set @s=@s+' from (select px=(select count(1) from tb where 卡号=t.卡号 and id<=t.id),* from tb t) t group by 卡号'
exec(@s)
/*
卡号 时间 卡号 时间 卡号 时间
------------ ---------- ------------ ---------- ------------ ----------
80005 2009-02-21
80030 2009-02-21 80030 2009-04-19
80049 2009-02-21
80051 2009-02-14 80051 2009-03-21
80124 2009-05-16
80180 2009-04-11 80180 2009-04-18 80180 2009-04-25(6 行受影响)
*/
drop table #tcreate table #t(ID int,time datetime ,money int)
insert into #t
select 1 ,'2009-08-01' ,1000
union all select 1 ,'2009-08-02' ,2000
union all select 2 ,'2009-08-15' ,5000
union all select 2 ,'2009-08-16' ,6000
union all select 2 ,'2009-08-17' ,7000
alter table #t add id1 int
go
declare @i int
declare @j int
set @i = 0
set @j = 0update #t set
id1 = case
when id = @j then @i else 1
end
,@i= case
when id = @j then @i+1 else 1
end
,@j = idselect * from #tdeclare @str varchar(8000)
set @str =''select @str = @str +',max(case when id1 ='+ltrim(id1)+' then time else null end)
,sum(case when id1 ='+ltrim(id1)+' then money else null end)'
from #t
where id = (select top 1 id from #t order by id1 desc)set @str = 'select id '+@str+' from #t group by id'--print @str
exec(@str)id
---- ------------------------ ----------- ------------------------ ----------- ------------------------ -----------
1 2009-08-01 00:00:00.000 1000 2009-08-02 00:00:00.000 2000 NULL NULL
2 2009-08-15 00:00:00.000 5000 2009-08-16 00:00:00.000 6000 2009-08-17 00:00:00.000 7000
这是以前类似的需求 给LZ参考