这个SQL语句怎么写？帮帮忙

[enjoynothing]

表ROOM：ROOMID NAME ROOMAREA DESCRIPT
—————————————————————
105 Mary 150 dweller
306 IBM 180 company
307 IBM 200 company
308 IBM 120 company
505 Linda 150 dweller
506 Linda 180 dweller
709 P&G 250 company
__________________________________________

最好能得到如下结果：
ROOMID NAME ROOMAREA DESCRIPT
—————————————————————
105 Mary 150 dweller
306,307,308 IBM 500 company
505,506 Linda 330 dweller
709 P&G 250 company
________________________________________

这样也行：
NAME ROOMAREA DESCRIPT
——————————————————
IBM 500 company
Linda 330 dweller

________________________________________
谢谢了

[zhbname]

up

[enjoynothing]

谢谢，第一种方案圆满解决。如果硬要挑毛病的话，好象第二种方案还是不行，我不要mary ,P&G的记录。

[friendliu]

select name,sum(roomarea) as roomarea,descipt from room
group by name,decript

[llsky]

SELECT NAME,SUM(ROMMAREA),DESCRIPT FROM ROOM GROUP GY NAME,DESCRIPT

[Cocoky]

第二种：
SELECT DISTINCT NAME,ROMMAREA,DESCRIPT FROM ROOM

[yujohny]

第一种：先写一个函数
CREATE FUNCTION GETSTR(@NAME NVARCHAR(20))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)

SET @str=''

SELECT @str = @str+ROOMID+','
FROM ROOM WHERE NAME =@NAME

SET @str = LEFT(@str,LEN(@str)-1)
RETURN @str
END

然后调用
select dbo.getstr([NAME]) AS ROOMID,[NAME],SUM(ROOMAREA) AS ROOMAREA,DESCRIPT
FROM ROOM GROUP BY [NAME],DESCRIPT

第二种：
select [NAME],SUM(ROOMAREA) AS ROOMAREA,DESCRIPT
FROM ROOM GROUP BY [NAME],DESCRIPT

[waitwater]

create table #table(ROOMID varchar(10), NAME varchar(10), ROOMAREA int, DESCRIPT char(10))
insert #table
values(105,'Mary',150,'dweller')

insert #table
values(306,'IBM',180,'company')
insert #table
values(307,'IBM',200,'company')
insert #table
values( 308,'IBM',120,'company')
insert #table
values( 505,'Linda',150,'dweller')
insert #table
values( 506,'Linda',180,'dweller')
insert #table
values( 709,'P&G', 250,'company')

create table #table2 (ROOMID varchar(8000), NAME varchar(10), ROOMAREA int, DESCRIPT char(10))

insert #table2(NAME,ROOMAREA,DESCRIPT)
select name,sum(roomarea) as roomarea,descript from #table
group by name,descript


declare @i int,@temp int
select @temp=count(*) from #table
set @i=0
declare @tempid varchar(10)
while @i<@temp
begin
SELECT top 1 *
INTO #table3
FROM #table


update #table2
set roomid=isnull(b.roomid,'')+a.roomid+','
from #table3 a,#table2 b
where a.name=b.name and a.descript=b.descript

select @tempid=roomid from #table3
IF exists (select * from #table where #table.roomid=@tempid )
begin
delete #table where roomid=@tempid
end
drop table #table3
set @i=@i+1
end
select * from #table2
drop table #table
drop table #table2

[aierong]

create table a( ROOMID varchar(100), NAME varchar(100), ROOMAREA smallint, DESCRIPT varchar(100))
insert into a values('105','Mary',150,'dweller')
insert into a values('306', 'IBM', 180 ,'company')
insert into a values('307', 'IBM' , 200 ,'company')
insert into a values('308', 'IBM', 120 , 'company')
insert into a values('505' , 'Linda' ,150 ,'dweller')
insert into a values('506', 'Linda', 180 ,'dweller')
insert into a values( '709', 'P&G' , 250 ,'company')
select * from a


创建表

CREATE FUNCTION funa(@NAME NVARCHAR(20))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @str NVARCHAR(200)
SET @str=''
SELECT @str = @str+ROOMID+',' FROM a WHERE NAME =@NAME
SET @str = LEFT(@str,LEN(@str)-1)
RETURN @str
END
再创建函数

select dbo.funa(NAME) AS ROOMID,NAME,SUM(ROOMAREA) AS ROOMAREA,DESCRIPT
FROM a GROUP BY NAME,DESCRIPT

查询结果