F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 李征 2009-07-06 13:20:00.000
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 方向 2009-07-06 13:20:00.000
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 成功 2009-07-06 13:20:00.000
得出这样的数据
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 李征;方向;成功; 2009-07-06 13:20:00.000
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 方向 2009-07-06 13:20:00.000
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 成功 2009-07-06 13:20:00.000
得出这样的数据
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 李征;方向;成功; 2009-07-06 13:20:00.000
长度固定的话:
charindex + substring
id varchar(100),
num varchar(13),
val int,
name varchar(10),
dt datetime
)insert into test
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'李征','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'方向','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'成功','2009-07-06 13:20:00.000' declare @t varchar(1000)
set @t = ''
select @t = @t + ';' + name from test where id='F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86' and num='1200907070004' and val=0 and dt='2009-07-06 13:20:00.000'
select stuff(@t, 1, 1, '')
合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go 合并表: SQL2000用函数: go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select
a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct COl1 from Tab) a
Cross apply
(select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select
a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query(' <Tab>
{for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")}
{concat("",string(/Tab[last()]/@COl2))}
</Tab>')
)b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1
union all
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f (3 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-22 13:55:33
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([col1] varchar(50),[col2] varchar(20),[col3] int,[col4] varchar(4),[col5] datetime)
insert [tb]
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'李征','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'方向','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'成功','2009-07-06 13:20:00.000'
--------------开始查询--------------------------
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 varchar(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tb where Col1=@Col1
return @S
end
go
Select distinct Col1,col2,col3,Col4=dbo.F_Str(Col1),col5 from tb go
------查询结果-------------------------------------
/*
Col1 col2 col3 Col4 col5
-------------------------------------------------- -------------------- ----------- ---------------------------------------------------------------------------------------------------- ------------------------------------------------------
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 1200907070004,1200907070004,1200907070004 2009-07-06 13:20:00.000(所影响的行数为 1 行)*/
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-22 13:55:33
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([col1] varchar(50),[col2] varchar(20),[col3] int,[col4] varchar(4),[col5] datetime)
insert [tb]
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'李征','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'方向','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'成功','2009-07-06 13:20:00.000'
--------------开始查询--------------------------
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 varchar(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col4 from Tb where Col3=@Col1
return @S
end
go
Select distinct Col1,col2,col3,Col4=dbo.F_Str(Col3),col5 from tb go
------查询结果-------------------------------------
/*Col1 col2 col3 Col4 col5
-------------------------------------------------- -------------------- ----------- ---------------------------------------------------------------------------------------------------- ------------------------------------------------------
F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86 1200907070004 0 李征,方向,成功 2009-07-06 13:20:00.000(所影响的行数为 1 行)
*/
create table test(
id varchar(100),
num varchar(13),
val int,
name varchar(10),
dt datetime
)insert into test
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'李征','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'方向','2009-07-06 13:20:00.000' union all
select 'F2FF4064-FCA2-4EE7-9BA7-00AE27D6AA86','1200907070004',0,'成功','2009-07-06 13:20:00.000' declare @id varchar(100), @num varchar(50), @val int, @name varchar(10), @dt datetime,
@old_id varchar(100), @old_num varchar(50), @old_val int, @old_dt datetimedeclare test_cursor cursor for
select id, num, val, dt, name
from test
order by id, num, val, dt, namedeclare @s varchar(1000)
set @s = ''
open test_cursorfetch next from test_cursor
into @id, @num, @val, @dt, @name
set @old_id = @id
set @old_num = @num
set @old_val = @val
set @old_dt = @dt
while @@FETCH_STATUS = 0
begin
if @id = @old_id and @num = @old_num and @val = @old_val and @dt = @old_dt
begin
set @s = @s + ',' + @name
end
else
begin
set @old_id = @id
set @old_num = @num
set @old_val = @val
set @old_dt = @dt
end
fetch next from test_cursor
into @id, @num, @val, @dt, @name
end
close test_cursor
deallocate test_cursor
select stuff(@s, 1, 1, '')