表格如下:
id1 time c1 c2
1 9:05 a b
1 9:15 c a
1 9:17 c b
2 9:02 b a
2 9:06 a c
2 9:37 b c
3 .. . .
.. .. . .希望得到如下结果id1 c1+c2
1 abcacb
2 baacbc
3 ......
按照id1分组。时间排序
id1 time c1 c2
1 9:05 a b
1 9:15 c a
1 9:17 c b
2 9:02 b a
2 9:06 a c
2 9:37 b c
3 .. . .
.. .. . .希望得到如下结果id1 c1+c2
1 abcacb
2 baacbc
3 ......
按照id1分组。时间排序
解决方案 »
- windows 2003 sp2装不了msn??急
- 网络操作excel ------------------------------------
- 向mysql同时插入50W条数据的存储过程该怎么写?
- 关于触发器的问题。。。。。。。。。。。。。。。。。。。。。。。
- 有条件的筛选重复记录怎么实现
- 往hbase中存大量数据时,代码运行一段时间后出错,求帮助
- 本地数据文件与远程SQL SERVER连接查询问题.
- BCP与Function Sequence Error
- 求助
- 如何用ASP连接sql server数据库
- 帮忙看下这个存储过程,急,在线等!!
- SQL Server 2005: 求ENDENGPOINT的实际应用举例
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
drop table tb
go
create table tb(id1 int,time varchar(20),c1 varchar(20),c2 varchar(20))
insert into tb select 1,'9:05','a','b'
insert into tb select 1,'9:15','c','a'
insert into tb select 1,'9:17','c','b'
insert into tb select 2,'9:02','b','a'
insert into tb select 2,'9:06','a','c'
insert into tb select 2,'9:37','b','c'select
a.id1,c=stuff(b.c.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct id1 from tb) a
Cross apply
(select c=(select c1+c2 from tb where id1=a.id1 For XML PATH(''), ROOT('R'), TYPE))bid1 c
1 bcacb
2 aacbc
drop function F_Str
go
create function F_Str(@id1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S,'')+c1+c2 from Tab where id1 =@id1
return @S
end
go
Select distinct id1,c1+c2 =dbo.F_Str(id1) from @tb
if object_id('tb') is not null
drop table tb
go
create table tb(id1 int,time varchar(20),c1 varchar(20),c2 varchar(20))
insert into tb select 1,'9:05','a','b'
insert into tb select 1,'9:15','c','a'
insert into tb select 1,'9:17','c','b'
insert into tb select 2,'9:02','b','a'
insert into tb select 2,'9:06','a','c'
insert into tb select 2,'9:37','b','c'create function F_Str(@id1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+'','')+c1+c2 from tb where id1=@id1
return @S
end
go
Select distinct id1,c=dbo.F_Str(id1) from tbid1 c
1 abcacb
2 baacbc
1 abcacb
declare @tb table([id1] int,[time] nvarchar(10),[c1] nvarchar(1),[c2] nvarchar(1))
Insert @tb
select 1,'9:05',N'a',N'b' union all
select 1,'9:15',N'c',N'a' union all
select 1,'9:17',N'c',N'b' union all
select 2,'9:02',N'b',N'a' union all
select 2,'9:06',N'a',N'c' union all
select 2,'9:37',N'b',N'c'
declare @tbB table(px int identity(1,1),[id1] int,[time] nvarchar(10),[c1] nvarchar(1),[c2] nvarchar(1))
insert into @tbB select * from @tbselect
max(id1) as id1,
max(case (px-1)%3 when 0 then c1 else '' end) as c1,
max(case (px-1)%3 when 0 then c2 else '' end) as c2,
max(case (px-1)%3 when 1 then c1 else '' end) as c3,
max(case (px-1)%3 when 1 then c2 else '' end) as c4,
max(case (px-1)%3 when 2 then c1 else '' end) as c5,
max(case (px-1)%3 when 2 then c2 else '' end) as c6
from @tbB group by (px-1)/3
/*
id1 c1 c2 c3 c4 c5 c6
----------- ---- ---- ---- ---- ---- ----
1 a b c a c b
2 b a a c b c(2 row(s) affected)
*/