0

IS there a way in SQL to make multiple rows for a common column, show up in one?

For example I would like this output:

enter image description here

to show up as:enter image description here

Chemdawg
  • 71
  • 9

1 Answers1

1

I believe this article contains solutions to your question -

SQL Query to concatenate column values from multiple rows in Oracle

procopypaster
  • 401
  • 6
  • I tried LISTAGG but it shows up with boxes between each character in the list. – Chemdawg Dec 09 '21 at 01:32
  • 1
    With LISTAGG you specify the delimiter in the second argument - I believe in your case it would be LISTAGG(Size, ',') – procopypaster Dec 09 '21 at 01:47
  • I understand but I am getting special characters between texts: select caseid, listagg(sku, ',') within group (order by sku) from pickdetail group by caseid; and i get the sku with special characters between. – Chemdawg Dec 09 '21 at 01:50
  • 1
    Do you have the the Oracle version that's needed? Looks like LISTAGG requires Oracle 11g Release 2. It may be worth trying some of the other options in there like XMLAGG and COLLECT. I don't work out of Oracle so not as familiar with it's functionality. – procopypaster Dec 09 '21 at 01:57
  • Thanks for the help. I really like the LISTAGG feature, but wish it would work. I should have the latest Oracle. It is "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production" – Chemdawg Dec 09 '21 at 02:25
  • 1
    It does look nice - I wonder if you could brute force the special characters with a replace()? Or maybe there's different documentation for that release? – procopypaster Dec 09 '21 at 02:27
  • I tried REPLACE("SIZE",'☐','') but that didn't work either :(. I guess it just doesnt support the function. – Chemdawg Dec 09 '21 at 03:30
  • data scientist in my company showed me how to fix it. forcing the field TO_CHAR worked. Thanks @procopypaster – Chemdawg Dec 09 '21 at 21:36
  • just curious in case I ever come across this, did you force TO_CHAR before or after LISTAGG? – procopypaster Dec 10 '21 at 02:03
  • After Listagg, so LISTAGG(TO_CHAR(Size), ', ') – Chemdawg Dec 14 '21 at 20:40