求一SQL语句

lixinramaxel41658133 2009-09-20 10:10:26

a b type
1 11 type1
1 11 type2
1 11 type3
1 22 type1
1 22 type2
1 22 type3
1 33 type3
1 33 type4
1 44 type4
1 44 type5
转化成
a b type
1 11 type1,type2,type3
1 22 type1,type2,type3
1 33 type3,type4
1 44 type4,type5

...全文

57 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

lixinramaxel41658133 2009-09-20

打赏
举报

黛玉MM果然厉害，，向小F学习。。

--小F-- 2009-09-20

打赏
举报

---2000这样

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-09-20 10:15:02

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([a] int,[b] int,[type] varchar(5))

insert [tb]

select 1,11,'type1' union all

select 1,11,'type2' union all

select 1,11,'type3' union all

select 1,22,'type1' union all

select 1,22,'type2' union all

select 1,22,'type3' union all

select 1,33,'type3' union all

select 1,33,'type4' union all

select 1,44,'type4' union all

select 1,44,'type5'

--------------开始查询--------------------------

CREATE FUNCTION dbo.f_strUnite(@id int) 

RETURNS varchar(8000) 

AS 

BEGIN 

    DECLARE @str varchar(8000) 

    SET @str = '' 

    SELECT @str = @str + ',' + type FROM tb WHERE b=@id 

    RETURN STUFF(@str, 1, 1, '') 

END 

GO 

-- 调用函数 

SELECT a, type = dbo.f_strUnite(b) FROM tb GROUP BY a,b 

drop table tb 

drop function dbo.f_strUnite 

go

----------------结果----------------------------

/* a           b           type

----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           11          type1,type2,type3

1           22          type1,type2,type3

1           33          type3,type4

1           44          type4,type5



(4 行受影响)

*/

--小F-- 2009-09-20

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2009-09-20 10:15:02

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([a] int,[b] int,[type] varchar(5))

insert [tb]

select 1,11,'type1' union all

select 1,11,'type2' union all

select 1,11,'type3' union all

select 1,22,'type1' union all

select 1,22,'type2' union all

select 1,22,'type3' union all

select 1,33,'type3' union all

select 1,33,'type4' union all

select 1,44,'type4' union all

select 1,44,'type5'

--------------开始查询--------------------------

select a,b, [type]=stuff((select ','+[type] from tb t where b=tb.b for xml path('')), 1, 1, '') 

from tb 

group by a,b 

----------------结果----------------------------

/* a           b           type

----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           11          type1,type2,type3

1           22          type1,type2,type3

1           33          type3,type4

1           44          type4,type5



(4 行受影响)

*/