select * from (
select list1_1.* from
(select 1 as A, 'aa' as Name,100 as Qty where 1=1)
as list1_1
union
select list1_2.* from
(select 2 as A, 'aa' as Name, 200 as Qty where 1=1)
as list1_2
) list2--这条语句查询结果是这样的:
A Name Qty
----------- ---- -----------
1 aa 100
2 aa 200
我想 根据 Name字段 group by 后,sum Qty字段, A字段 的值,使用 逗号隔开..
我想的结果是这样的:A Name Qty
----------- ---- -----------
1,2 aa 300http://topic.csdn.net/u/20110608/17/73a5264f-1023-48e6-97c3-c94a7d1cf32f.html
这个贴子,我曾经问过,,但那 是我数据库的一个表的,可以那样做。。
现在问题是:
我是查出的一个结果集的...
该怎么写额..
select list1_1.* from
(select 1 as A, 'aa' as Name,100 as Qty where 1=1)
as list1_1
union
select list1_2.* from
(select 2 as A, 'aa' as Name, 200 as Qty where 1=1)
as list1_2
) list2--这条语句查询结果是这样的:
A Name Qty
----------- ---- -----------
1 aa 100
2 aa 200
我想 根据 Name字段 group by 后,sum Qty字段, A字段 的值,使用 逗号隔开..
我想的结果是这样的:A Name Qty
----------- ---- -----------
1,2 aa 300http://topic.csdn.net/u/20110608/17/73a5264f-1023-48e6-97c3-c94a7d1cf32f.html
这个贴子,我曾经问过,,但那 是我数据库的一个表的,可以那样做。。
现在问题是:
我是查出的一个结果集的...
该怎么写额..
(
select 1 as A, 'aa' as Name,100 as Qty union
select 2 as A, 'aa' as Name, 200 as Qty
)select A=stuff((select ','+rtrim(A) from cte where name=a.name for xml path('')),1,1,''),
name,SUM(qty) qty from cte a group by name
/*
A name qty
---- ---- -----------
1,2 aa 300(1 行受影响)
AS
(
select list1_1.* from
(select 1 as A, 'aa' as Name,100 as Qty where 1=1)
as list1_1
union
select list1_2.* from
(select 2 as A, 'aa' as Name, 200 as Qty where 1=1)
as list1_2
)
SELECT A=STUFF((SELECT ','+RTRIM(A) FROM list2 WHERE NAME=a.NAME FOR XML PATH('')),1,1,''),
Name,
SUM(Qty) AS Qty
FROM list2 AS a
GROUP BY Name/*
A Name Qty
1,2 aa 300
*/
(
select list1_1.* from
(select 1 as A, 'aa' as Name,100 as Qty where 1=1)
as list1_1
union
select list1_2.* from
(select 2 as A, 'aa' as Name, 200 as Qty where 1=1)
as list1_2
) select
A=stuff((select ','+ltrim(a) from list2 where name=t.name for xml path('')),1,1,''),
Name,
sum(Qty) as Qty
from list2 t
group by name