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 行受影响) */
create function fn_test(@kcmc varchar(50),@zbsbz varchar(50)) return varchar(1000) declare @str varchar(8000) select @str = ','+jsxm from tb where zbsbz = @zbsbz and kcmc = @kcmc set @str = stuff(@str,1,1,'') return @strselect kcmc,zbsbz,jsxm= dbo.fn_test(@kcmc,@zbsbz) from tb
if object_id('tb') is not null drop table tb go create table tb(zbsbz varchar(50),jsxm varchar(50),kcmc varchar(50),kcdm varchar(50)) insert into tb select '备授','a','综合英语(一)','000000051' insert into tb select '备授','b','综合英语(一)','000000051' insert into tb select '主授','c','综合英语(一)','000000051' insert into tb select '主授','d','综合英语(一)','000000051' insert into tb select '主授','e','日语','000000091' insert into tb select '主授','f','俄语','000000691' insert into tb select '主授','g','体育','000000731' if object_id('f_hb') is not null drop function f_hb go create function f_hb(@zbsbz varchar(50),@kcmc varchar(50)) returns varchar(8000) as begin declare @re varchar(8000) select @re=isnull(@re+',','')+jsxm from tb where zbsbz=@zbsbz and case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end=@kcmc group by jsxm return @re end go select case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end as 'kcmc', zbsbz, dbo.f_hb(zbsbz,case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end) as 'hb' from tb group by kcmc,zbsbz order by kcmc desckcmc zbsbz hb 综合英语 备授 a,b 综合英语 主授 c,d 体育 主授 g 日语 主授 e 俄语 主授 f
if object_id('tb') is not null drop table tb go create table tb(zbsbz varchar(10),jsxm varchar(10),kcmc varchar(20),kcdm varchar(20)) insert into tb select '备授','a','综合英语(一)','000000051' insert into tb select '备授','b','综合英语(一)','000000051' insert into tb select '主授','c','综合英语(一)','000000051' insert into tb select '主授','d','综合英语(一)','000000051' insert into tb select '主授','e','日语','000000091' insert into tb select '主授','f','俄语','000000691' insert into tb select '主授','g','体育','000000731'if object_id('f_hb') is not null drop function f_hb go create function f_hb(@zbsbz varchar(50),@kcmc varchar(50)) returns varchar(8000) as begin declare @re varchar(8000) select @re=isnull(@re,'')+','+jsxm from tb where zbsbz=@zbsbz and kcmc=@kcmc set @re=stuff(@re,1,1,'') return @re end goselect kcmc,zbsbz ,jsxm=dbo.f_hb(zbsbz,kcmc) from tb group by kcmc,zbsbz order by kcmc desc/* kcmc zbsbz jsxm -------------------- ---------- -------------------- 综合英语(一) 备授 a,b 综合英语(一) 主授 c,d 体育 主授 g 日语 主授 e 俄语 主授 f(所影响的行数为 5 行)*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
应该是这个,
里面有函数的,
自己改改就星了,
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 行受影响)
*/
return varchar(1000)
declare @str varchar(8000)
select @str = ','+jsxm from tb where zbsbz = @zbsbz and kcmc = @kcmc
set @str = stuff(@str,1,1,'')
return @strselect kcmc,zbsbz,jsxm= dbo.fn_test(@kcmc,@zbsbz) from tb
drop table tb
go
create table tb(zbsbz varchar(50),jsxm varchar(50),kcmc varchar(50),kcdm varchar(50))
insert into tb select '备授','a','综合英语(一)','000000051'
insert into tb select '备授','b','综合英语(一)','000000051'
insert into tb select '主授','c','综合英语(一)','000000051'
insert into tb select '主授','d','综合英语(一)','000000051'
insert into tb select '主授','e','日语','000000091'
insert into tb select '主授','f','俄语','000000691'
insert into tb select '主授','g','体育','000000731'
if object_id('f_hb') is not null
drop function f_hb
go
create function f_hb(@zbsbz varchar(50),@kcmc varchar(50))
returns varchar(8000)
as
begin
declare @re varchar(8000)
select @re=isnull(@re+',','')+jsxm from tb where zbsbz=@zbsbz and case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end=@kcmc group by jsxm
return @re
end
go
select
case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end as 'kcmc',
zbsbz,
dbo.f_hb(zbsbz,case when charindex('(',kcmc)>0 and charindex(')',kcmc)>0 then stuff(kcmc,charindex('(',kcmc),charindex('(',kcmc)-charindex('',kcmc),'') else kcmc end) as 'hb'
from tb
group by kcmc,zbsbz
order by kcmc desckcmc zbsbz hb
综合英语 备授 a,b
综合英语 主授 c,d
体育 主授 g
日语 主授 e
俄语 主授 f
drop table tb
go
create table tb(zbsbz varchar(10),jsxm varchar(10),kcmc varchar(20),kcdm varchar(20))
insert into tb select '备授','a','综合英语(一)','000000051'
insert into tb select '备授','b','综合英语(一)','000000051'
insert into tb select '主授','c','综合英语(一)','000000051'
insert into tb select '主授','d','综合英语(一)','000000051'
insert into tb select '主授','e','日语','000000091'
insert into tb select '主授','f','俄语','000000691'
insert into tb select '主授','g','体育','000000731'if object_id('f_hb') is not null
drop function f_hb
go
create function f_hb(@zbsbz varchar(50),@kcmc varchar(50))
returns varchar(8000)
as
begin
declare @re varchar(8000)
select @re=isnull(@re,'')+','+jsxm from tb where zbsbz=@zbsbz and kcmc=@kcmc
set @re=stuff(@re,1,1,'')
return @re
end
goselect kcmc,zbsbz ,jsxm=dbo.f_hb(zbsbz,kcmc) from tb group by kcmc,zbsbz order by kcmc desc/*
kcmc zbsbz jsxm
-------------------- ---------- --------------------
综合英语(一) 备授 a,b
综合英语(一) 主授 c,d
体育 主授 g
日语 主授 e
俄语 主授 f(所影响的行数为 5 行)*/