Group Multiple Rows Into Single Row

Question

I have the table below:

|  ID  |  Description|  Shelf  |  QTY  |
----------------------------------------
|  10  | Apples      |    1    |   24  |
|  10  | Apples      |    2    |   28  |
|  10  | Apples      |    6    |   12  |
|  15  | Oranges     |    2    |   8   |
|  15  | Oranges     |    6    |   33  |

I need to do some updating to this table and put the Shelf and QTY in together as a group as shown below:

|  ID  |  Description|    Availability    |
-------------------------------------------
|  10  | Apples      |  1-24, 2-28, 6-12  |
|  15  | Oranges     |  2-8, 6-33         |

Basically, we are trying to group the shelf and QTY in one single line. This is for reporting purposes only so I have to display the data in that way.

So far, I am able to display the two columns by concatenating them and adding the dash:

SELECT ID, Description, Shelf || '-' || QTY AS Availability FROM tbl_Products

To stitch them up together in a single line, I'm pretty sure I can use a WHILE loop to loop through the values and create the Availability string field before inserting it back to a temporary table then printing that out to the report.

But I'm not too sure about the performance. Since this query will be run in a big table, i'm just worried that it will slow down the database each time the report is being pulled.

So is there any other easier way I can achieve this?

Answer 1

string_agg will fit the bill exactly:

SELECT   id, description, STRING_AGG(shelf || '-' || qty, ', ') AS Availability
FROM     tbl_Products
GROUP BY id, description