• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
andrew_optimum
Asked:
andrew_optimum
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
mlmccCommented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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:

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


Good Luck,
Kent
0
 
vastoCommented:
I assume the table name is CustomerSupport . Replace the name before to run  the SQL
SELECT cst.customerid, 
         Stuff((SELECT ', ' + c.Support FROM CustomerSupport c WHERE c.customerid = cst.customerid For XML PATH ('')),1,1,'') as Support
FROM  (SELECT DISTINCT customerid FROM CustomerSupport ) cst

Open in new window



You can also check this thread:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27828897.html
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now