How to merge multiple row in a single rows in sql

Question

Here is my sample table.(Sql)

City UserId UserName Campagin
Mumbai 1    Smita    TeachIndia
Mumbai 1    Smita    SaveTree
Mumbai 1    Smita    CleanCity
Pune   2    Rashmi   GreenCity
Pune   2    Rashmi    SaveTree

I want output like this

City UserId UserName Campagin
Mumbai 1    Smita    TeachIndia,SaveTree,CleanCity
Pune   2    Rashmi   GreenCity,SaveTree

How can I achieve it?

tag your question with proper dbms – Fahmi Jan 03 '20 at 06:51 — Fahmi, Jan 03 '20 at 06:51

score 0 · Answer 1 · edited Jan 03 '20 at 08:17

0

If it's SQL Server, then you can try this:

SELECT 
    city, userid, username, 
    campaign = STUFF((SELECT ',' + campaign 
                      FROM tbl t1
                      WHERE t1.userid = t2.userid
                      FOR XML PATH ('')), 1, 1, '') 
FROM
    tbl t2
GROUP BY
    city, userid, username

edited Jan 03 '20 at 08:17

marc_s

732,580
175
1,330
1,459

answered Jan 03 '20 at 06:03

Red Devil

2,343
2
21
41

I have added city also in where clause.Then it work. – swati Jan 03 '20 at 07:26

score 0 · Answer 2 · answered Jan 03 '20 at 06:06

0

You can refer to below links for multiple possible solutions over this: https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

SQL Server: combining multiple rows into one row

answered Jan 03 '20 at 06:06

Sudhanshu

1
1
3

How to merge multiple row in a single rows in sql

2 Answers2