Single Row Subquery that returns multiple results to comma list. 2

[CassidyHunt]

Basically I am wanting to know how you would take a query that has say 15 rows and turn that into one row comma delimited. The problem I am trying to solve is that I have a work order order that feeds multiple sales orders with different desired want dates. When I prioritize the work order in the system for the number of parts I need at a given time I want to be able to show the scheduler all the desired want dates instead of just one. That way they can make a decision on lot size to reduce total on hand inventory.

Hopefully that makes sense. Thanks in advance

Cassidy

 

[SantaMufasa]

Cassidy,

It's been months since we heard from you! Where have you been, young man? <grin>

I'm thinking that the wm_concat function is what you want:

select * from s_item order by ord_id;

ORD_ID    ITEM_ID PRODUCT_ID      PRICE   QUANTITY
------ ---------- ---------- ---------- ----------
    97          1      20106          9       1000
    97          2      30321       1500         50
    98          1      40421         85          7
    99          1      20510          9         18
    99          2      20512          8         25
    99          3      50417         80         53
    99          4      50530         45         69
   100          1      10011        135        500
   100          2      10013        380        400
   100          3      10021         14        500
   100          4      10023         36        400
   100          5      30326        582        600
   100          6      30433         20        450
   100          7      41010          8        250
   101          1      30421         16         15
   101          2      40422         50         30
   101          3      41010          8         20
   101          4      41100         45         35
   101          5      50169       4.29         40
   101          6      50417         80         27
   101          7      50530         45         50
   102          1      20108         28        100
   102          2      20201        123         45
   103          1      30433         20         15
   103          2      32779          7         11
   104          1      20510          9          7
   104          2      20512          8         12
   104          3      30321       1669         19
   104          4      30421         16         35
   105          1      50273      22.89         16
   105          2      50419         80         13
   105          3      50532         47         28
   106          1      20108         28         46
   106          2      20201        123         21
   106          3      50169       4.29        125
   106          4      50273      22.89         75
   106          5      50418         75         98
   106          6      50419         80         27
   107          1      20106         11         50
   107          2      20108         28         22
   107          3      20201        115        130
   107          4      30321       1669         75
   107          5      30421         16         55
   108          1      20510          9          9
   108          2      20512          8         18
   108          3      30321       1669         85
   108          4      32779          7         60
   108          5      32861         60         57
   108          6      41080         35         50
   108          7      41100         45         42
   109          1      10011        140        150
   109          2      10012        175        600
   109          3      10022      21.95        300
   109          4      30326        582       1500
   109          5      30426      18.25        500
   109          6      32861         60         50
   109          7      50418         75         43
   110          1      50273      22.89         17
   110          2      50536         50         23
   111          1      40421         65         27
   111          2      41080         35         29
   112          1      20106         11         50

62 rows selected.

select ord_id,wm_concat(product_id) product
from s_item
group by ord_id
order by 1
/

ORD_ID PRODUCT
------ -----------------------------------------
    97 20106,30321
    98 40421
    99 20510,20512,50417,50530
   100 10011,10021,30326,41010,30433,10023,10013
   101 30421,41010,50169,50530,50417,41100,40422
   102 20108,20201
   103 30433,32779
   104 20510,30421,30321,20512
   105 50273,50419,50532
   106 20108,50169,50273,50419,50418,20201
   107 20106,30421,30321,20201,20108
   108 20510,41100,41080,20512,30321,32861,32779
   109 10011,50418,32861,10012,10022,30426,30326
   110 50273,50536
   111 40421,41080
   112 20106

16 rows selected.

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

BTW, Cassidy, it's even simpler if you are wanting to string together the product numbers for a specific ord_id:

select wm_concat(product_id) product
from s_item
where ord_id = 108;

select wm_concat(product_id) product
from s_item
where ord_id = 108;

PRODUCT
-----------------------------------------
20510,20512,30321,32779,32861,41080,41100

1 row selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[taupirho]

Cassidy, the only caveat to Dave's excellent (as usual) response to your question is to note that the WM_CONCAT feature is unsupported by Oracle. If that's a concern please see the link below for a number of examples of alternative solutions

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Good catch, Taupirho. I wasn't aware that Oracle was no longer promoting the very-useful WM_CONCAT () to newer versions. (Their mistake, IMHO.)

Hava

for your good catch and a good link.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CassidyHunt]

Thank you. Always can count on the answer here. I have been overwhelmed in my life personally and professionally as of late. If you want to catch up offline or on facebook let me know.

Cassidy