CODEWITHSUNDEEP
sql
DanilGholtsman
DanilGholtsman

Reputation: 2374

SQL concat rows with same name value

Let say I got table like that

Name | Stage | Date
-------------------
A    |  1st |  03092014    
A    |  2nd |  04092014    
A    |  3rd |  05092014    
B    |  1st |  06092014    
B    |  2nd |  08092014    
C    |  1st |  03092014

I wonder how to write SQL code wich would concat rows with same names and I will get something like that

Name | Stage          | Date
----------------------+-----------------------------
A    | 1st , 2nd, 3rd |  03092014 04092014 05092014    
B    | 1st, 2nd       |  06092014 08092014    
C    | 1st            |  03092014

Do I need to run through table with for cycle or is there better way to do that?

UPD: I found out that I need to use this queries in Excel

Upvotes: 0

Views: 152

Answers (2)

Murtaza
Murtaza

Reputation: 3065

With respect to your question - I am assuming you are using MS SQL Server 2008 or higher to get he desired output

I would suggest to use CROSS APPLY here to concat the data -

Assumed Your Table name - temptable

SELECT distinct tblMain.Name, substring(stages, 1, len(stages)-1) as [Stage],substring(dates, 1, len(dates)-1) as [Date]  
FROM temptable tblMain
CROSS APPLY (
    SELECT LTRIM(RTRIM(Stage)) + ',' 
    FROM temptable tblDup1 WITH(NOLOCK)
    WHERE tblDup1.Name= tblMain.Name

     FOR XML PATH('')
) t1 (stages)
CROSS APPLY (
    SELECT LTRIM(RTRIM(Date)) + ' ' 
    FROM temptable tblDup2 WITH(NOLOCK)
    WHERE tblDup2.Name= tblMain.Name

     FOR XML PATH('')
) t2 (dates)

Working FIDDLE OUTPUT

Upvotes: 1

Mats Kindahl
Mats Kindahl

Reputation: 2075

You can use GROUP_CONCAT for this:

SELECT Name
     , GROUP_CONCAT(Stage) AS Stages
     , GROUP_CONCAT(Date) AS Dates
  FROM my_table
GROUP BY Name;

Upvotes: 3

Related Questions