How to group multiple values into a single column in SQL

Question

Given the following SQL

SELECT 
    T1."PN" as "Part Number",
    T2."QTY" as "Quantity",
    T2."BRANCH" AS "Location", T3."STOCK" as "Bin"
FROM 
    "XYZ"."PARTS" T1,
    "XYZ"."BALANCES" T2,
    "XYZ"."DETAILS" T3
WHERE (T2."PART_ID" = T1."PART_ID") AND (T3."PART_ID" = T1."PART_ID")
ORDER BY "Part Number" ASC, "Location" ASC

We get results such as

YZ-7-CA-080                 88  01  STOCK7
YZ-7-CA-080                 88  01  03482 
YZ-7-CA-080                 88  01  A8K2D

For location 01, there are 88 pieces of that part number YZ-7-CA-080 and they can be found in any of the 3 bins STOCK7, 03482, or A8K2D. The location value refers to a common branch like a warehouse and the quantity is for the entire warehouse, not the bins.

I need to change the output so we can write out instead one entry with bins as a list

YZ-7-CA-080                 88  01  STOCK7,03482,A8K2D

So I am looking for a good way to do this refactoring of the results in SQL. I feel there should be a way to use a function or subquery or something like that and hoping there is a single multi-db solution but assume there could be a need for different solutions on different dbs. (Oracle is the primary solution we are trying to solve but secondary priority db we need this for is SQL Server).

Note: There are multiple locations per part number so its not enough to set distinct on the first column to reduce the multiple part number entries. There would be multiple of the same part number at location 02 also with the same issue.

Ideas?

Which `RDBMS` are you actually using? Sounds like Oracle, if so, use `LISTAGG`... — sgeddes, Jun 18 '14 at 00:53
Oracle first and primary yes, but we also are building a connector for SQL Server to do similar later. — Streamline, Jun 18 '14 at 00:55

Gordon Linoff · Answer 1 · 2014-06-18T02:22:51.933

1

You can do this in Oracle using listagg():

SELECT T1."PN" as "Part Number", max(T2."QTY") as "Quantity", T2."BRANCH" AS "Location",
       listagg(T3."STOCK") within group (order by  t3.stock) as Bins
FROM "XYZ"."PARTS" T1 JOIN
     "XYZ"."BALANCES" T2
     ON T2."PART_ID" = T1."PART_ID" JOIN
     "XYZ"."DETAILS" T3
     ON T3."PART_ID" = T1."PART_ID"
GROUP BY t1.PN, t2.Branch
ORDER BY "Part Number", "Location";

I also fixed the query to use proper explicit join syntax.

edited Jun 18 '14 at 02:22

answered Jun 18 '14 at 01:49

Gordon Linoff

1,242,037
58
646
786

Looks promising... Thanks of the correction with the join. I tried setting it up and I am getting "Error Message: ORA-00923: FROM keyword not found where expected" for some reason. – Streamline Jun 18 '14 at 02:07
It is not obvious to me why that error would occur. – Gordon Linoff Jun 18 '14 at 02:13
Ok, I will try to figure it out. I assume the BY BY in the GROUP BY is typo? – Streamline Jun 18 '14 at 02:14
@Streamline . . . Yes, yes. – Gordon Linoff Jun 18 '14 at 02:22
After some research, I believe the reason the error is shown is because LISTAGG is not available until 11gR2 and I confirmed our customer's database is 10g. – Streamline Jun 18 '14 at 03:16
@Streamline . . . As Nishanti points out, `wm_concat()` is available in earlier versions. – Gordon Linoff Jun 18 '14 at 10:42
thanks. Your solution seems the way to go if my customer wasn't using the older version of the database. I will see if Nishanti's use of wm_concat() solves it as expected. – Streamline Jun 18 '14 at 13:10

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

you can use substrings and subqueries, applying distinct in the first column

a similar technique is described here in this question: Concatenate many rows into a single text string?

edited May 23 '17 at 12:10

Community

1
1

answered Jun 18 '14 at 00:53

bovino Marcelo Bezerra

2,042
16
28

score 0 · Accepted Answer · edited May 23 '17 at 10:30

For older versions, I guess WM_CONCAT would work. Modifying Gordon Linoff's query:

SELECT T1."PN" as "Part Number", max(T2."QTY") as "Quantity", T2."BRANCH" AS "Location",
       WM_CONCAT(T3."STOCK") as Bins
FROM "XYZ"."PARTS" T1 JOIN
     "XYZ"."BALANCES" T2
     ON T2."PART_ID" = T1."PART_ID" JOIN
     "XYZ"."DETAILS" T3
     ON T3."PART_ID" = T1."PART_ID"
GROUP BY t1.PN, t2.Branch
ORDER BY "Part Number", "Location";

Also refer this link for an alternate approach: Including the answer in the link for refernce:

create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');


SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua

I've read that WM_CONCAT is unsupported and undocumented and other posts where people suggest not using it. What is the risk of using the command if it doesn't appear to have any issues when testing it? Does it put the database at risk in any way? — Streamline, Jun 18 '14 at 12:22
I tried your answer with the WM_CONCAT and it does work, thanks! Again, I wonder about any potential consequences to using the unsupported command. — Streamline, Jun 18 '14 at 13:12
The second portion of your answer that uses the substr doesn't make it clear to me how to combine the more complex table joins and selects for my current sql. Is that supposed to be a subquery to my sql? — Streamline, Jun 18 '14 at 13:15

How to group multiple values into a single column in SQL

3 Answers3