Help with combining data from multiple rows into one column in a view

[gaholmes@comcast.net]

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.


I have a view that looks sort of like this (but with a lot more
entries of course)


UniqueIdentifyi er Column1 Column2
1 9999 100
2 9999 200
3 9999 300


What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.

UniqueIdentifyi er Column1 Column2 Column3
1 9999 100 100, 200, 300
2 9999 200 100, 200, 300
3 9999 300 100, 200, 300

[Erland Sommarskog]

Re: Help with combining data from multiple rows into one column in a view

(gaholmes@comca st.net) writes:

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.
>
>
I have a view that looks sort of like this (but with a lot more
entries of course)
>
>
UniqueIdentifyi er Column1 Column2
1 9999 100
2 9999 200
3 9999 300
>
>
What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.
>
UniqueIdentifyi er Column1 Column2 Column3
1 9999 100 100, 200, 300
2 9999 200 100, 200, 300
3 9999 300 100, 200, 300

This may not be doable in a view, particularly if you are on SQL 2000.

SQL Server MVP Anith Sen has a collection of methods to attack this
problem at http://www.projectdmx.com/tsql/rowconcatenate.aspx.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx