Simple(?) SQL grouping question

[cambam]

OK, I want to return a result set that looks similar to this:

id region
----- -------
00001 US
EUR
ASIA
00002 EUR
ASIA
00003 SA
US

What's happening here is that the id is the same where the blank spaces are. So, id 00001 has regions US, EU, and ASIA. Is this possible with simple SQL or do I need to write a stored proc?

 

[tlbroadbent]

Actually, I prefer to use a reporting tool to produce this kind of result. Although, you can produce the result or something near the result you desire, SQL isn't really the best way to handle this.

Here is a SQL method that may give you an idea how to start.

Select
-- Determine if the ID should have a value
-- or spaces depending on the value of region
ID1=Case
When Region =
-- Use a sub query
(Select Min(region) From #tmp Where ID=t.ID)
-- I used an ineger value for ID
-- so it has to be converted
Then ltrim(str(ID))
Else '' End,
Region
From #tmp t
Order By Id, Region Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.