Shkedy
Mon Jan 22 17:56:29 CST 2007
Not too elegent but if your table is called br this can be done with
cursors:
SELECT DISTINCT Building, CAST('' AS Varchar(300)) AS SUMMARY_ROOMNUMBER
INTO #Temp
FROM br
DECLARE @building varchar(200)
DECLARE @roomNumber varchar(200)
DECLARE br_cursor CURSOR FOR SELECT building, roomnumber FROM br
OPEN br_cursor
FETCH NEXT FROM br_cursor INTO @building, @roomnumber
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp SET SUMMARY_ROOMNUMBER = SUMMARY_ROOMNUMBER + ',' +
@roomNumber WHERE building = @building
FETCH NEXT FROM br_cursor INTO @building, @roomnumber
END
CLOSE br_cursor
DEALLOCATE br_cursor
UPDATE #Temp SET SUMMARY_ROOMNUMBER = SUBSTRING(SUMMARY_ROOMNUMBER, 2,
LEN(SUMMARY_ROOMNUMBER))
WHERE SUMMARY_ROOMNUMBER LIKE ',%'
SELECT * FROM #Temp
DROP TABLE #Temp
Sagi Shkedy
http://blog.shkedy.com
"Jay Balapa" <jbalapa@hotmail.com> wrote in message
news:uaKcNonPHHA.448@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I have a table as follows-
>
> BUILDING ROOM_NUMBER
> MAIN 100
> MAIN 101
> MAIN 102
> SUB 200
> SUB 202
>
> The Resultset should be as follows-
>
> BUILDING SUMMARY_ROOMNUMBE
> MAIN 100,101,102
> SUB 200,202
>
> I know I need to use the group by clause but I dont know how to append the
> summary room number. Any help would be greatly appreaciated.
>
> Thanks.
>
> -jay
>