SQL View Question

[rob11nj]

I'm using MSSQL 2005 and am trying to create a view that does the following.

If the dataset looks like this:

Column A Column B
---------------------
host1 ABC
host2 AAA
host1 CDE
host3 FFF
host2 BDE
host1 ZZZ

The output of the view would be something like this:

Column A Column B
---------------------
host1 ABC,CDE,ZZZ
host2 AAA,BDE
host3 FFF

So basically column A will be distinct records and column B will be a string that contains all of the column A records separated by a comma. Does anyone know of a simple way to accomplish this in a view?

Thanks!

 

[gmmastros]

Does this have to be a view, or can you use a stored procedure with a User defined function?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Vongrunt posted a link last week that exmplains 1 method for doing this. This method won't work in a view.

http://www.nigelrivett.net/SQLTsql/CSVStringFromTableEntries.html

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rob11nj]

Thanks gmmastros for the link and quick response.

I'm really looking for a way to do this in a view though. Any suggestions would be very much appreciated.