tmp_aaaid type_id fn name
1 13 1 你好
2 75 2 你来
3 0 3 自哪个
4 0 4 国家?
5 90 5 你吃
6 0 6 饭了吗?type_id 为 0 时表示换行用一SQL语句变成如下id type_id fn name
1 13 1 你好
2 75 2 你来自哪个国家?
3 90 3 你吃饭了吗
1 13 1 你好
2 75 2 你来
3 0 3 自哪个
4 0 4 国家?
5 90 5 你吃
6 0 6 饭了吗?type_id 为 0 时表示换行用一SQL语句变成如下id type_id fn name
1 13 1 你好
2 75 2 你来自哪个国家?
3 90 3 你吃饭了吗
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-28 11:29:57
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[type_id] int,[fn] int,[name] nvarchar(4))
Insert tb
Select 1,13,1,'你好' union all
Select 2,75,2,'你来' union all
Select 3,0,3,'自哪个' union all
Select 4,0,4,'国家?' union all
Select 5,90,5,'你吃' union all
Select 6,0,6,'饭了吗?'
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select id,[type_id]=(select max([type_id]) from tb where id<=t.id),fn,name
from tb t
)
select id=row_number()over(order by getdate()),type_id,fn=row_number()over(order by getdate()),
name=ltrim((select ''+name from t where type_id=a.type_id for xml path('')))
from t a
group by type_id
/*
id type_id fn name
-------------------- ----------- -------------------- ------------------------------
1 13 1 你好
2 75 2 你来自哪个国家?
3 90 3 你吃饭了吗?(3 行受影响)*/
-------------------------------------------> 测试时间:2009-07-28
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[tmp_aaa]') is not null drop table [tmp_aaa]
create table [tmp_aaa]([id] int,[type_id] int,[fn] int,[name] varchar(7))
insert [tmp_aaa]
select 1,13,1,'你好' union all
select 2,75,2,'你来' union all
select 3,0,3,'自哪个' union all
select 4,0,4,'国家?' union all
select 5,90,5,'你吃' union all
select 6,0,6,'饭了吗?'select *,flag=case when type_id>0 then type_id else (select max(type_id) from tmp_aaa where t.id>id) end into temp_tab from [tmp_aaa] tCREATE FUNCTION dbo.f_str(@flag int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
set @r=''
SELECT @r = @r + name FROM temp_tab WHERE flag=@flag
return @r
END
GOselect id=(SELECT COUNT(DISTINCT flag)+1 FROM temp_tab WHERE flag<a.flag),
flag,
fn=(SELECT COUNT(DISTINCT flag)+1 FROM temp_tab WHERE flag<a.flag),
name=dbo.f_str(flag)
from temp_tab a
group by flag/*
id flag fn name
----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 13 1 你好
2 75 2 你来自哪个国家?
3 90 3 你吃饭了吗?(所影响的行数为 3 行)
*/drop table tmp_aaa,temp_tab
drop FUNCTION dbo.f_str
Drop table [tb]
Go
Create table [tb]([id] int,[type_id] int,[fn] int,[name] nvarchar(4))
Insert tb
Select 1,13,1,'你好' union all
Select 2,75,2,'你来' union all
Select 3,0,3,'自哪个' union all
Select 4,0,4,'国家?' union all
Select 5,90,5,'你吃' union all
Select 6,0,6,'饭了吗?'
Go
--Select * from tb-->SQL查询如下:select id,[type_id]=(select max([type_id]) from tb where id<=t.id),fn,name into #k
from tb t
select * from #k
--2005的方法 select id=row_number()over(order by [type_id]),[type_id],fn=row_number()over(order by [type_id]),*
from (
select distinct [type_id],
name=ltrim((select ''+name from #k where type_id=a.type_id for xml path('')))
from #k a) t
/*
id type_id fn type_id name
-------------------- ----------- -------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 13 1 13 你好
2 75 2 75 你来自哪个国家?
3 90 3 90 你吃饭了吗?(3 行受影响)
*/
if object_id('test') is not null
drop table test
create table test(id int,type_id int,fn int,name varchar(7))
insert test
select 1,13,1,'你好' union all
select 2,75,2,'你来' union all
select 3,0,3,'自哪个' union all
select 4,0,4,'国家?' union all
select 5,90,5,'你吃' union all
select 6,0,6,'饭了吗?'create function fn_test
(
@fn int
)
returns varchar(500)
as
begin
declare @maxfn int,@str varchar(500),@type intselect @maxfn=max(fn) from test
select @str=name from test where fn = @fn
set @fn=@fn+1
while @fn <= @maxfn
begin
select @type=type_id from test where fn = @fn
if(@type = 0)
select @str=@str+name from test where fn = @fn
else
break
set @fn = @fn + 1
end
return @str
end
goselect id,type_id,fn,dbo.fn_test(fn) from test where type_id <> 0
/**
id type_id fn name
----------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 13 1 你好
2 75 2 你来自哪个国家?
5 90 5 你吃饭了吗?(所影响的行数为 3 行)
**/