combine different row data into one cell in sql [duplicate]

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
– Nikhil

2019-04-18 15:58:57 +00:00
Commented Apr 18, 2019 at 15:58 — Nikhil
– Nikhil, Commented Apr 18, 2019 at 15:58

Nikhil · Accepted Answer · 2019-04-18 16:05:38Z

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, 2019 at 16:05

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Kaushal Over a year ago

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

Caius Jard · Accepted Answer · 2019-04-18 16:03:41Z

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, 2019 at 16:03

answered Apr 18, 2019 at 15:57

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Collectives™ on Stack Overflow

combine different row data into one cell in sql [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related