求一SQL,越精辟越好,续贴...

[ZXY900213]

刚刚已经发了一个贴：http://topic.csdn.net/u/20110110/10/a7f72255-3057-4b59-9d36-9be0598b38dc.html
一个叫“爱新觉罗.毓华”的哥们答的很详细，但是我发现还有点问题，所以发个续贴，希望“爱新觉罗.毓华”大哥再指点下

-----------------------------------
Type        Name
A           A_1
B           B_1
A           A_2
A           A_3
A           A_4
B           B_2
B           B_3

想要的效果如下：
-----------------------------------
Type        Name_String
A           A_1;A_2;A_3;A_4
B           B_1;B_2;B_3

就是这个问题，我用你说的第二种方法：

create table tb(Type varchar(10), Name varchar(10))
insert into tb values('A' , 'A_1')
insert into tb values('B' , 'B_1')
insert into tb values('A' , 'A_2')
insert into tb values('A' , 'A_3')
insert into tb values('A' , 'A_4')
insert into tb values('B' , 'B_2')
insert into tb values('B' , 'B_3')
go

select type, [Name] = stuff((select ';' + [Name] from tb t where type = tb.type for xml path('')) , 1 , 1 , '')
from tb
group by type

发现查询出来的Name_String并没有按Type分类，而是相同的数据。
-------------------------------------
Type Name_String
A A_1;B_1;A_2;A_3;A_4;B_2;B_3
B A_1;B_1;A_2;A_3;A_4;B_2;B_3

[ZXY900213]

为什么我查的结果是这样子呢？

WITH [Temp]([Type],[Name]) AS
(
	SELECT 'A' AS [Type] , 'A_1' AS [Name] UNION ALL
	SELECT 'B' , 'B_1' UNION ALL
	SELECT 'A' , 'A_2' UNION ALL
	SELECT 'A' , 'A_3' UNION ALL
	SELECT 'A' , 'A_4' UNION ALL
	SELECT 'B' , 'B_2' UNION ALL
	SELECT 'B' , 'B_3'
)
SELECT [Type],[Name]=Stuff((SELECT ',' + [Name] FROM [Temp] AS T WHERE [Type] = T.[Type] FOR XML PATH('')) , 1 , 1 , '')
FROM [Temp]
GROUP BY [Type]

结果：
---------------------------------
Type            Name
A               A_1,B_1,A_2,A_3,A_4,B_2,B_3
B               A_1,B_1,A_2,A_3,A_4,B_2,B_3

[AcHerat]

WITH [Temp]([Type],[Name]) AS
(
    SELECT 'A' AS [Type] , 'A_1' AS [Name] UNION ALL
    SELECT 'B' , 'B_1' UNION ALL
    SELECT 'A' , 'A_2' UNION ALL
    SELECT 'A' , 'A_3' UNION ALL
    SELECT 'A' , 'A_4' UNION ALL
    SELECT 'B' , 'B_2' UNION ALL
    SELECT 'B' , 'B_3'
)
SELECT [Type],[Name]=Stuff((SELECT ',' + [Name] FROM [Temp] AS T WHERE [Temp].[Type] = T.[Type] FOR XML PATH('')) , 1 , 1 , '')
FROM [Temp]
GROUP BY [Type]

Type Name
---- -----------
A    A_1,A_2,A_3,A_4
B    B_1,B_2,B_3

SELECT ',' + [Name] FROM [Temp] AS T WHERE [Temp].[Type] = T.[Type] FOR XML PATH('')这里，你的是 [Type] = T.[Type] 应该是 [Temp].[Type] = T.[Type]

[dawugui]

你还是用创建表的形式来做吧.

[ZXY900213]

[Quote=引用 7 楼 acherat 的回复:]

SQL code

WITH [Temp]([Type],[Name]) AS
(
SELECT 'A' AS [Type] , 'A_1' AS [Name] UNION ALL
SELECT 'B' , 'B_1' UNION ALL
SELECT 'A' , 'A_2' UNION ALL
SELECT 'A' , 'A_3' UNION ALL
……
[/Quote]
果然是这样...

[gw6328]

SELECT [Type],[Name]=Stuff((SELECT ',' + [Name] FROM [Temp] AS T WHERE x.[Type] = T.[Type] FOR XML PATH('')) , 1 , 1 , '')
FROM [Temp] x

加个别名就行了

[chu12345632]

不错！大哥像你们学习哦

[百年树人]

select type, [Name] = stuff((select ';' + [Name] from tb t where type = t.type for xml path('')) , 1 , 1 , '')
from tb t
group by type

[华夏小卒]

drop table tb 
go
create table tb(Type varchar(10), Name varchar(10))
insert into tb values('A' , 'A_1')
insert into tb values('B' , 'B_1')
insert into tb values('A' , 'A_2')
insert into tb values('A' , 'A_3')
insert into tb values('A' , 'A_4')
insert into tb values('B' , 'B_2')
insert into tb values('B' , 'B_3')
go

select type, [Name] = stuff((select ';' + [Name] from tb where type = t.type for xml path('')) , 1 , 1 , '')
from tb t
group by type


type       Name
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A          A_1;A_2;A_3;A_4
B          B_1;B_2;B_3

(2 行受影响)

[dawugui]

这个语句是sql 2005中用的.我这里测试没有问题.

create table tb(Type varchar(10), Name varchar(10))
insert into tb values('A' , 'A_1')
insert into tb values('B' , 'B_1')
insert into tb values('A' , 'A_2')
insert into tb values('A' , 'A_3')
insert into tb values('A' , 'A_4')
insert into tb values('B' , 'B_2')
insert into tb values('B' , 'B_3')
go

select type, [Name] = stuff((select ';' + [Name] from tb t where type = tb.type for xml path('')) , 1 , 1 , '')
from tb
group by type

drop table tb

/*
type       Name
---------- ----------------
A          A_1;A_2;A_3;A_4
B          B_1;B_2;B_3

(2 行受影响)
*/