Hi there

I have the following two tables

mainprofile (profile varchar(20), description)
accprofile (profile varchar(20), acct_type int)

Sample data could be

mainprofile
----------------
prof1 | profile one
prof2 | profile two
prof3 | profile three

accprofile
--------------

prof1 | 0
prof1 | 1
prof1 | 2
prof2 | 0

Now doing a join between these two tables would return multiple rows,
but I would like to know whether it would be possible to return
acct_type horizontally in a column of the result set, e.g.

prof1 | profile one | [0,1,2]
prof2 | profile two | [0]

I could probably manage this with cursors, but it would be very
resource intensive. Is there a better way?

Regards,
Louis

For a one time data display or if this is used by a single application or a
report, you should consider retrieving the resultset to the client side,
leverage the display/presentation language's string manipulative features
and appropriately format the data there.

If this is more of a general requirement and used by several applications,
in certain cases it may make some sense to do it at the server using t-SQL.
For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith

[quoted text, click to view]

Thanks. In the end I decided to stick with using a CURSOR

Regards,
Louis