一个很难很难的查询，不知道能不能实现？

[zhenliang]

我查询语句完成如下操作
表名：test
字段名： a b c quantity (a 、b、c作为主键)
值： a1 b1 c1 1
a1 b2 c2 2
a1 b2 c3 1
a2 b1 c1 1
a2 b3 c1 1
要求以a字段进行分组对quantity求和，并且把该分组中不重复b、c以逗号为间隔显示。
要求结果：groupA unionB unionC sumQuantity
a1 b1,b2 c1,c2,c3 4
a2 b1,b3 c1 2

[xluzhong]

参考:
http://blog.csdn.net/xluzhong/articles/263039.aspx

[xiajianfeng]

--try

CREATE FUNCTION GetUnion (@StrA VARCHAR(100), @Type VARCHAR(1))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @b AS VARCHAR(100)
DECLARE @c AS VARCHAR(100)
DECLARE @RetName AS VARCHAR(100)
SET @b = ''
SET @c = ''
SET @RetName = ''
DECLARE My_Cursor CURSOR FOR
SELECT RTRIM(b), RTRIM(c) FROM test WHERE a = @StrA
OPEN My_Cursor
FETCH NEXT FROM My_Cursor
INTO @b, @c
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'b' AND CHARINDEX(@b, @RetName) = 0
SET @RetName = @RetName + ',' + RTRIM(@b)
IF @Type = 'c' AND CHARINDEX(@c, @RetName) = 0
SET @RetName = @RetName + ',' + RTRIM(@c)
FETCH NEXT FROM My_Cursor
INTO @b, @c
END
CLOSE My_Cursor
DEALLOCATE My_Cursor

RETURN(RIGHT(@RetName, LEN(@RetName) - 1))
END

--调用
SELECT a AS groupA, dbo.GetUnion(a, 'b') AS unionB, dbo.GetUnion(a, 'c') AS unionC, SUM(quantity) AS sumQuantity FROM test
GROUP BY a

[Softlee81307]

Create Table Test(a varchar(3),b varchar(3),c varchar(3),quantity int)
insert into test
select 'a1','b1','c1',1 union all
select 'a1','b2','c2',2 union all
select 'a1','b2','c3',1 union all
select 'a2','b1','c1',1 union all
select 'a2','b3','c1',1
-----------建產一個函數----------------------
Create Function Getb(@b varchar(3) )
returns varchar(100)
as
begin
Declare @s varchar(100)
set @s=''
select @s=@s+b+',' from test where a=@b Group by a,b
set @s=substring(@s,1,len(@s)-1)
return(@s)
end
------------------------第二個函數-------
Create Function GetC(@b varchar(3) )
returns varchar(100)
as
begin
Declare @s varchar(100)
set @s=''
select @s=@s+c+',' from test where a=@b Group by a,c
set @s=substring(@s,1,len(@s)-1)
return(@s)
end
-----------------------------下面調用函數隻用-執行下面一條語句就可------------
Select a,b=dbo.Getb(a),c=dbo.Getc(a),sum(Quantity) from test group by a
------結果--------------------------
groupA unionB unionC sumQuantity
a1 b1,b2 c1,c2,c3 4
a2 b1,b3 c1 2


drop function Getb
drop function getc
drop table test

[zanyzyg]

写个函数，处理 b,c，然后分组查询就可以了！