Multiple values into one row

[Crystalguru]

I have a query that returns multiple rows because of a code that describes a Unit. Can I write a SQL statement that will put all of the codes in one row?
For example:
select unit_num,amenity_code from UNITS

It returns:
Unit_Num Amenity_code
312 B
312 K
312 C

And the list can go on!!

Can I get the output to look like this:
Unit_num Amenity_code
312 BKC

Thanks!

 

[rac2]

Per tlbroadbent

Do a keyword search for "crosstab" in this forum. You'll find many examples. Here are three threads you may find useful.

Thread183-44374
Thread183-323405
Thread183-392875

Or search on "rows into columns" in the database forums.

 

[Crystalguru]

Thanks! I will take a look. Seems other people run into this also.