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 TabgoSQL2005用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>') )bSQL05用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 行受影响) */
DECLARE @S VARCHAR(10) SELECT @S=ISNULL(@S+',','')+NAME FROM TA SELECT @S
DECLARE @S VARCHAR(10) SELECT @S=ISNULL(@S+',','')+NAME FROM TA SELECT @S楼主字段命名方式还是要改进一下,不要用关键字来做字段名称。
declare @A table ( [ID] int,[name] varchar(30))insert @A select 1,'小明' union all select 2,'小红' union all select 3,'小刚' select * from @A declare @S varchar(100) select @S=IsNull(@S+',','') +Name from @A select @S
declare @A table ( [ID] int,[name] varchar(30))insert @A select 1,'小明' union all select 1,'小红' union all select 1,'小刚' SELECT * FROM( SELECT DISTINCT id FROM @A )A OUTER APPLY( SELECT [names]= STUFF(REPLACE(REPLACE( ( SELECT name FROM @A N WHERE id = A.id FOR XML AUTO ), '<N name="', ','), '"/>', ''), 1, 1, '') )N
SELECT @S=ISNULL(@S+',','')+NAME FROM TA
SELECT @S
set @name=''
select @name=@name+name from a
select @name
/******************************************************************************************************************************************************
合并分拆表数据整理人:中国风(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 TabgoSQL2005用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>')
)bSQL05用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 行受影响)
*/
SELECT @S=ISNULL(@S+',','')+NAME FROM TA
SELECT @S
SELECT @S=ISNULL(@S+',','')+NAME FROM TA
SELECT @S楼主字段命名方式还是要改进一下,不要用关键字来做字段名称。
declare @A table (
[ID] int,[name] varchar(30))insert @A
select 1,'小明' union all
select 2,'小红' union all
select 3,'小刚' select * from @A
declare @S varchar(100)
select @S=IsNull(@S+',','') +Name from @A
select @S
[ID] int,[name] varchar(30))insert @A
select 1,'小明' union all
select 1,'小红' union all
select 1,'小刚'
SELECT *
FROM(
SELECT DISTINCT
id
FROM @A
)A
OUTER APPLY(
SELECT
[names]= STUFF(REPLACE(REPLACE(
(
SELECT name FROM @A N
WHERE id = A.id
FOR XML AUTO
), '<N name="', ','), '"/>', ''), 1, 1, '')
)N