Returning values from mulitple records as a single column

There are times where you want to rollup single column data from mulitple records and present as a single column as part of a result set. Sure, Pivot does essentially the same thing, but you must know the values of the data and the pivot returns multiple columns, not mutliple values as one column.

The solution below comes in very handy for reporting purposes where you really want 1 line of data with associated with mulitple columns.

Enjoy..

DECLARE @StateList VARCHAR(MAX), @ClientID varchar(32)
SET @ClientID = 'YOUR_CLIENT_ID_HERE'
SELECT  @StateList = COALESCE(@StateList + ' , ','') + ClientStateList.state 
from
 (select distinct state 
  from claddress 
     where clientid = @ClientID) as ClientStateList

select name, @StateList as USLocations from cl where ClientID = @ClientID