碰到难题，大家帮忙

[newqq]

A01 200
B01 300

想要得到

A01,B01 500

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-28 16:01:53
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([a] varchar(3),[b] int)
insert [tb]
select 'A01',200 union all
select 'B01',300
--------------开始查询--------------------------
select a,b
from
(
select id,a=stuff((select ','+ltrim([b]) from (select id=1,* from tb)a where id=t.id for xml path('')), 1, 1, '') ,sum(b) as b
from (select id=1,* from tb)t 
group by id 
)t
----------------结果----------------------------
/* a                                                                                                                                                                                                                                                                b
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
200,300                                                                                                                                                                                                                                                          500

(1 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-28 16:01:53
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([a] varchar(3),[b] int)
insert [tb]
select 'A01',200 union all
select 'B01',300
--------------开始查询--------------------------
DECLARE @STR VARCHAR(8000)

SELECT @STR=ISNULL(@STR+',','')+a FROM (SELECT DISTINCT a FROM tb)AS T

SELECT @STR,sum(b) from tb

----------------结果----------------------------
/* ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
A01,B01                                                                                                                                                                                                                                                          500

(1 行受影响)
*/

[playwarcraft]

就2个栏位？？

declare @s varchar(1000), @q int
select @s=isnull(@s,'')+','+col1, @q=isnull(@q,0) +col2 from T
select stuff(@s,1,1,''),@q

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-28 16:01:53
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([a] varchar(3),[b] int)
insert [tb]
select 'A01',200 union all
select 'B01',300
--------------开始查询--------------------------
select a,b
from
(
select id,a=stuff((select ','+ltrim([a]) from (select id=1,* from tb)a where id=t.id for xml path('')), 1, 1, '') ,sum(b) as b
from (select id=1,* from tb)t 
group by id 
)t


----------------结果----------------------------
/* a                                                                                                                                                                                                                                                                b
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
A01,B01                                                                                                                                                                                                                                                          500

(1 行受影响)
*/

[快乐_石头]

if object_id('[t]') is not null drop table [t]
go
create table [t] (A nvarchar(6),B int)
insert into [t]
select 'A01',200 union all
select 'B01',300
select 
     stuff((select ','+A from t for xml path('')),1,1,''),
     sum(b)
from t
*/

[快乐_石头]

[Quote=引用 6 楼 fredrickhu 的回复:]
SQL code----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-12-28 16:01:53
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00?-
[/Quote]
沒必要嵌那麼多吧

[newqq]

2楼的大哥Happy_Stone
我刚试了你的，如果是
A01 200
B01 300
C01 400
.
.
.
要怎么做

[SQL77]

if object_id('[t]') is not null drop table [t]
go
create table [t] (A nvarchar(6),B int)
insert into [t]
select 'A01',200 union all
select 'B01',300


alter  FUNCTION GET_STRING(@a VARCHAR(10))
RETURNS VARCHAR(50)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(50)
       SELECT @CLASS=ISNULL(@CLASS+',','')+a FROM t WHERE right(a,2)=right(@a,2)
       RETURN @CLASS
  END

select distinct dbo.GET_STRING(t.a)as a ,t1.b

from t ,(select right(a,2) a,sum(b)b from t group by right(a,2))as t1
where right(t.a,2)=t1.a

a                                                  b           
-------------------------------------------------- ----------- 
A01,B01                                            500

（所影响的行数为 1 行）

[快乐_石头]

if object_id('[t]') is not null drop table [t]
go
create table [t] (A nvarchar(6),B int)
insert into [t]
select 'A01',200 union all
select 'B01',300 union all
select 'C01',400
declare @s varchar(100)
select @s=isnull(@s+',','')+a from t
select @s,sum(b)from t
/*
---------------------------------------------------------------------------------------------------- -----------
A01,B01,C01                                                                                          900

(1 個資料列受到影響)

*/

[快乐_石头]

--2005
if object_id('[t]') is not null drop table [t]
go
create table [t] (A nvarchar(6),B int)
insert into [t]
select 'A01',200 union all
select 'B01',300 union all
select 'C01',400
select 
     stuff((select ','+A from t for xml path('')),1,1,''),
     sum(b)
from t

[newqq]

SQL77的办法可以，Happy_Stone的在2000中报错第 7 行: 'xml' 附近有语法错误。

[--小F--]

[Quote=引用 12 楼 happy_stone 的回复:]
SQL code--2005ifobject_id('[t]')isnotnulldroptable[t]gocreatetable[t] (Anvarchar(6),Bint)insertinto[t]select'A01',200unionallselect'B01',300unionallselect'C01',400selectstuff((select','+Afrom tfor xml?-
[/Quote]

我自己想复杂了 哎

[newqq]

[Quote=引用 14 楼 fredrickhu 的回复:]
引用 12 楼 happy_stone 的回复:
SQL code--2005ifobject_id('[t]')isnotnulldroptable[t]gocreatetable[t] (Anvarchar(6),Bint)insertinto[t]select'A01',200unionallselect'B01',300unionallselect'C01',400selectstuff((select','+Afrom tfor xml?-


我自己想复杂了 哎
[/Quote]

这个语句在2000中会报错的

[快乐_石头]

[Quote=引用 13 楼 newqq 的回复:]
SQL77的办法可以，Happy_Stone的在2000中报错第 7 行: 'xml' 附近有语法错误。
[/Quote]
11樓2000寫法
12樓2005寫法

[newqq]

谢谢了，结贴

[feixianxxx]

wa

[fa_ge]

青青?

[newqq]

啊亮，是你？？

[fa_ge]

果真是我家青青啊，呵呵。你现在还在关内上班吧?

[lsd123]

.