0

I'm trying to take several rows and collapse the values in one column into a comma-separated list so that I end up with 1 row.

I have this:

SELECT tc.dv_task,
lob.sys_id

FROM task_ci tc
INNER JOIN u_cmdb_ci_line_of_business lob on tc.ci_item = lob.sys_id
WHERE tc.dv_task = 'INC1157655'

The output is several rows, all have the same INC#, but different sys ids.

I need the output to be one row, where first column is INC# and second column is a list of sys ids. Like this:

INC1157655      
 42cd17e713cd660000c0fc04e144b0f5, a6cd57e713cd660000c0fc04e144b038,  35ddd7e713cd660000c0fc04e144b074, etc

I tried STRING_AGG but got an error that that isn't a recognized function.

Any help appreciated.

Thanks, Chrissy

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Chrissy Scott
  • 29
  • 5
  • 2
    Which dbms are you using? (The answer will probably be product specific.) – jarlh Mar 20 '20 at 20:17
  • Also, what version of which dbms? – Isaac Mar 20 '20 at 20:20
  • I'm using SQL Server Management Studio v 17.9.1 – Chrissy Scott Mar 20 '20 at 20:44
  • You need the functions `LIST_AGG()` or `GROUP_CONCAT()`, but older versions of SQL Server don't support any of them. I think there was some workaround using XML functions. Google it. It was more cumbersome, but worked. – The Impaler Mar 20 '20 at 21:02
  • 1
    That isnt the version of SQL Server, that's the version of the IDE. what version of SqL Server are you using? The fact that `STRING_AGG` wasn't recognised implies 2016 or prior. – Thom A Mar 20 '20 at 21:03
  • I'm not sure what the SQL Server version is. – Chrissy Scott Mar 20 '20 at 21:23
  • What does this return? `select @@version` – Isaac Mar 20 '20 at 21:24
  • @@version returns: Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor) – Chrissy Scott Mar 20 '20 at 21:36
  • @EricBrandt, that's what I'm playing with now, but it's giving me 13 rows, all of them have the comma-separated list. Trying to sort out how to get it to be just one row for the one INC# SELECT --tc.dv_task, lob.name, (SELECT ',' + lob.name FROM u_cmdb_ci_line_of_business lob --WHERE tc.ci_item = lob.sys_id FOR XML PATH('')) [LOBs] FROM task_ci tc INNER JOIN u_cmdb_ci_line_of_business lob on tc.ci_item = lob.sys_id WHERE tc.dv_task = 'INC1157655' ORDER BY 1, 2 – Chrissy Scott Mar 20 '20 at 21:40
  • I suspect that in your outer query you just need a `GROUP BY tc.dv_task` or similar clause. – Eric Brandt Mar 20 '20 at 21:43
  • @EricBrandt, when I tried that I got an error saying Column 'task_ci.dv_task' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. I added it right after WHERE tc.dv_task = 'INC115755' – Chrissy Scott Mar 20 '20 at 21:50

0 Answers0