Append Room numbers fields by Building

  • Thread starter Thread starter Jay Balapa
  • Start date Start date
J

Jay Balapa

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
 
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
 
For a one time data display or if this is used by a single application or a
custom report, you should retrieve the resultset to the client side, utilize
the display/presentation language's string manipulation functionalities and
appropriately format the data there.

If this is more of a general requirement and used by several applications,
in few cases it may make some sense to do it at the server using t-SQL. For
some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
 
Anith said:
For a one time data display or if this is used by a single application or a
custom report, you should retrieve the resultset to the client side, utilize
the display/presentation language's string manipulation functionalities and
appropriately format the data there.

If this is more of a general requirement and used by several applications,
in few cases it may make some sense to do it at the server using t-SQL. For
some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
Click to expand...

Hi Anith,
I have problems trying to understand your logic. Suppose I have a
request for "a one time data display" in Excel as follows:

Agent name: Jane Doe
States Licensed In: IL,MI,WI,IN
From where I sit, it is much easier for me to just concatenate state
Click to expand...
codes in the database, as opposed to learning how to do it in Excel (I
am not sure it is doable in Excel) or involving another person
just for such a very simple task. Makes sence?
 
From where I sit, it is much easier for me to just concatenate state
Sure, given a specific limited premise, depending on what you consider to be
"much easier" and assuming un-restricted access to production data for ad
hoc querying, what your suggested is pretty possible. My suggestion was
somewhat generic and against creating a permanent procedural structure on
the server for such application specific requirements.
 
Back
Top