-----------------------------------------
--表 a(id1,id2)
id1 id2
2 1,2,3
2 3,5
3 2,3,5
3 5
--轉化成為表b
id1 id2
2 1
2 2
2 3
2 3
2 5
3 2
3 3
3 5
3 5
select * into b from a
delete b ---復制表結構到b
select * into #a from a
select * into #b from a
delete #b----建立測試環境----建立循環
declare @a int
set @a=1
while(@a<>0)
begin
insert into #b select * from #a where patindex('%,%',id2)=0
delete ll from #a ll,#b where ll.id1=#b.id1
insert into b select id1,left(id2, patindex('%,%',id2)-1) from #a
update #a set id2=stuff(id2,1,patindex('%,%',id2),'')
select @a=count(*) from #a
end----刪除測試環境
select *from #a
drop table #a,#b
select * from b order by id1 ---b表為所求
--表 a(id1,id2)
id1 id2
2 1,2,3
2 3,5
3 2,3,5
3 5
--轉化成為表b
id1 id2
2 1
2 2
2 3
2 3
2 5
3 2
3 3
3 5
3 5
select * into b from a
delete b ---復制表結構到b
select * into #a from a
select * into #b from a
delete #b----建立測試環境----建立循環
declare @a int
set @a=1
while(@a<>0)
begin
insert into #b select * from #a where patindex('%,%',id2)=0
delete ll from #a ll,#b where ll.id1=#b.id1
insert into b select id1,left(id2, patindex('%,%',id2)-1) from #a
update #a set id2=stuff(id2,1,patindex('%,%',id2),'')
select @a=count(*) from #a
end----刪除測試環境
select *from #a
drop table #a,#b
select * from b order by id1 ---b表為所求
--建立测试环境
Create Table 表(id varchar(10),amount integer,re varchar(10))
--插入数据
insert into 表
select '1','3','aaa' union
select '1','5','bbb' union
select '1','4','ccc' union
select '2','10','pkoge' union
select '2','12','daf'
go
--测试语句
CREATE FUNCTION FunMergeCharField(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+re FROM 表 WHERE id=@vchA
RETURN(substring(@r,2,8000))
END
GO
select id,sum(amount) as sum,dbo.FunMergeCharField(id) as re叠加 from 表 group by id
--删除测试环境
Drop Table 表
Drop FUNCTION FunMergeCharField/*
id sum re叠加
---------- ----------- ---------------
1 12 aaa,ccc,bbb
2 22 pkoge,daf
*/
--------------------------附帶將多行記錄轉換成一個逗號分割得字符串 userid 邊成1,2,3,4
declare @s varchar(8000)
select @s=@s+Userid+',' from 表
print @s
insert tb select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 2,'a'
union all select 2,'d'
union all select 3,'g'
go--查询处理
select A,B=min(B)
+CASE
WHEN COUNT(*)=3 THEN ','
+(SELECT B FROM tb WHERE A=a.A AND B NOT IN(MAX(a.B),MIN(a.B)))
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+max(a.B)
ELSE ''
END
from tb a
group by A
go--删除测试
drop table tb/*--结果
A B
----------- -----
1 a,b,c
2 a,d
3 g
--*/
Create table TEST
(A Int,
B Varchar(10))
GO
--插入数据
Insert TEST Values(1, 'a')
Insert TEST Values(1, 'b')
Insert TEST Values(1, 'c')
Insert TEST Values(2, 'a')
Insert TEST Values(2, 'd')
Insert TEST Values(3, 'g')
--建立函数
GO
CREATE FUNCTION GetB(@A Varchar(10))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SET @s=''
SELECT @s=@s+','+B FROM TEST WHERE A=@A
RETURN(Substring(@s,2,8000))
END
GO
--测试
Select A,B=dbo.GetB(A) from TEST Group By A
--删除测试环境
Drop table TEST
Drop FUNCTION GetB
--结果
/*
A B
1 a,b,c
2 a,d
3 g
*/
create function f_catString(@id int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+type from TEST where id=@id
return(stuff(@s,1,1,''))
-- stuff('string',start,length,'string')刪除指定長度的字符并在指定的起始點插入另一組字符
end
go --调用函数
select id,dbo.f_catString(id) as type from TEST group by id
go --删除测试环境
Drop Table TEST