combine different row data into one cell in sql

Question

I have a view with the following structure:

areas    employee_id     complaint_type_id
a1        e1                c1
a2        e1                c1
a3        e2                c1
a1        e1                c2
.

Now in the code, I am fetching area and complaint_type_id for a particular employee from view. I want to display the complaint categories assigned to an employee for different areas.

The output I am getting is as follows:

area       complaint_type_id
a1          c1
a2          c1
a3          c1
a1          c2
a2          c2
a3          c2

In this output, the areas are getting repeat for each kind of complaint id. I want to display complaint categories in a single row for a particular area

My desired output is as follows:

area          complaint_type_id
a1            c1,c2
a2            c1,c2
a3            c1,c2

I tried using the group by clause but in the output of the group by areas are appearing twice for each complaint type. How can I achieve my desired output?

use FOR XML PATH listagg equivalent!! – Nikhil S Apr 18 '19 at 15:58 — Nikhil S, Apr 18 '19 at 15:58

score 1 · Accepted Answer · answered Apr 18 '19 at 16:05

1

Take CTEs for other tables and this will work:

select distinct t1.area,
  STUFF((SELECT distinct ',' + t2.complaint_type_id
         from Table1 t2
         where t1.area = t2.area
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from Table1 t1;

check:http://sqlfiddle.com/#!18/0f121/7

answered Apr 18 '19 at 16:05

Nikhil S

3,786
4
18
32

your solution is working for me but it is fetching all type of complaint_type_id. I need only those which are assigned to a particular employee – Amit Kaushal Apr 18 '19 at 16:35

Caius Jard · Answer 2 · 2019-04-18T16:03:41.927

0

If you have a recent version of SQLS or SQLAzure:

SELECT
  area,
  STRING_AGG(complaint_type_id, ',') as complaint_type_id
FROM
  table
GROUP BY 
  area

ps; interesting article: https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation - the precis of which is "if you dont have a recent nough version of SQLS to have STRING_AGG, use STUFF/FOR XML PATH or the .NET CLR"

edited Apr 18 '19 at 16:03

answered Apr 18 '19 at 15:57

Caius Jard

72,509
5
49
80

combine different row data into one cell in sql

2 Answers2