asked on

SQL to join multiple rows data

Hi all

I have the following table:

CustomerID      Support
1            Training
1            Remote
1            Telephone
2            Remote
2            Internet

I would like to return in a SQL view the following:

CustomerID      Support
1            Training, Remote, Telephone
2            Remote, Internet

So basically I need to join the initial table into a string.

Thanks for your help

Guy Hengel [angelIII / a3]

please read this article for one of the possible options:
http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005

it's using the FOR XML subquery technique

Mike McCracken

Are you doing this for a Crystal Report?
If so you can use formulas to concatenate the values and display the result in a group footer

mlmcc

Kent Olsen

Hi Andrew,

The exact answer depends a lot on which SQL you're using. (Sounds silly, I know.)

If you have no more than 3 values for each ID, joining the rows to themself by ID is one way. Using SQL Server pivot is another. And recursive SQL is still another.

You might want to read this article for an understanding of recursive SQL:

https://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html

Good Luck,
Kent

ASKER CERTIFIED SOLUTION

vasto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial