Trying to consolidate multiple rows in one column with one ID

Question

I am trying to consolidate multiple rows with same id but different Concept in one row, with using ", " delimiter.

Tried this code but it is giving me multiple ConceptsID instead of one

select distinct vc.Employeeid ,
(select distinct (STRING_AGG(cast(ConceptId as varchar(max)), ', ') WITHIN GROUP (ORDER BY ConceptId ASC)) from Concepts c1 where c.ConceptId = c1.ConceptId ) AS concept
from employee e 
 left join v_CurrentClasses vc on vc.[EmployeeId]=e.[EmployeeId]
Left JOIN ClassSchedules cs
   ON vc.ClassScheduleId = cs.ClassScheduleId
left JOIN ClassCategories cc
   ON cc.ClassCategoryId = cs.ClassCategoryId
LEFT JOIN ClassTypes ct
   ON ct.ClassTypeId = cc.ClassTypeId and ct.CSIServiceId = cc.ClassCategoryId
inner JOIN Concepts c
   ON c.ConceptId = ct.ConceptId
   left join [JobTitles] jt
   on jt.JobTitleId=e.JobTitleId
   inner join clubs cb
   on cb.clubid=vc.clubid
  --where e.date>= getdate()
  group by vc.Employeeid, c.ConceptId
  order by 1

This is the output that is coming

Employeeid     conceptID
215             4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4
217             2, 2, 2, 2, 2, 2, 2, 2, 2, 2
217             4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4
217             8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8
232             2, 2, 2, 2, 2, 2, 2, 2, 2, 2
240             23, 23
240             6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
249             6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
249             8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8

I want this output

Employeeid     conceptID
215             4
217             2, 4, 8, 
232             2
240             23, 6
249             6, 8

try this maybe https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server — Kostya, May 17 '19 at 21:42
I tried Using this but it takes forever for the query to be completed. — Akki, May 20 '19 at 16:50

score 1 · Accepted Answer · answered May 20 '19 at 17:40

maybe something like this

SELECT 
    Employeeid
    ,STRING_AGG(cast(ConceptId as varchar(max)), ', ') WITHIN GROUP (ORDER BY ConceptId ASC) AS concept
FROM(
    select vc.Employeeid ,ConceptId
    from employee e 
     left join v_CurrentClasses vc on vc.[EmployeeId]=e.[EmployeeId]
    Left JOIN ClassSchedules cs
       ON vc.ClassScheduleId = cs.ClassScheduleId
    left JOIN ClassCategories cc
       ON cc.ClassCategoryId = cs.ClassCategoryId
    LEFT JOIN ClassTypes ct
       ON ct.ClassTypeId = cc.ClassTypeId and ct.CSIServiceId = cc.ClassCategoryId
    inner JOIN Concepts c
       ON c.ConceptId = ct.ConceptId
       left join [JobTitles] jt
       on jt.JobTitleId=e.JobTitleId
       inner join clubs cb
       on cb.clubid=vc.clubid
      --where e.date>= getdate()
      group by vc.Employeeid, c.ConceptId
    ) TB
order by 1

score 0 · Answer 2 · answered May 17 '19 at 21:58

0

Can't you just take out the STRING_AGG and select the distinct value?

answered May 17 '19 at 21:58

alexherm

1,362
2
18
31

Trying to consolidate multiple rows in one column with one ID

2 Answers2