CODEWITHSUNDEEP
oracle-databaseaggregate-functionslistagg
user76595
user76595

Reputation: 377

How to aggregate and string concatenate at same time in Oracle?

I have data that looks like this:

Customer      Product     Amount
   A           Table        500  
   A           Table        300
   A           Chair        100
   B           Rug           50
   B           Chair        200

And am Trying to get a result to look like this:

Customer      Product        Amount
   A           Table, Chair    900  
   B           Rug, Chair      250

In MSSQL, I could use for XML PATH('') and easily get there. I'm not as versed in Oracle. I've tried using LISTAGG, and here is what I get:

Customer      Product                 Amount
   A           Table,Table,Chair       1150  
   B           Rug, Chair              1150

So I'm getting duplicates in the Product string, and somehow the amounts are all the same. Looking into some similar questions on here I can't find how to do both the aggregate, and distinct listagg together. Here is my attempt:

Select
 Customer
,sum(Amount) as "Amount"
,LISTAGG(Product, ', ') WITHIN GROUP(Order by CUSTOMER) as "TestingThis"
From
(Select 
  A.Customer
 ,B.Product
 ,B.Amount
 ,Row_Number() over (Partition by A.Customer, B.Product order by A.Customer) as rn
  From
  CustomerTable A
  left join ProductTable B
  on A.ID = B.ID
  Group by
  A.Customer
 ,B.Product
 ,B.Amount
)
 where rn = 1
 Group by Customer

This gets be slightly closer and produces this:

Customer      Product        Amount
   A           Table, Chair    600
   B           Rug, Chair      250

Notice how it's only summing up when rn = 1, so it's missing the rest of the Amount.

Using Oracle 11g, so if they have some fancy 'distinct' feature for LISTAGG in newer versions, it won't help me.

Transitioning from MSSQL to PLSQL is painful. This would be so easy to do in MSSQL. I don't understand how LISTAGG could even/ever be useful when not able to get distinct values easily?

The closest solutions I have found on here do not account for doing another aggregation like the sum in addition to the distinct LISTAGG. They just say, 'if you need to do another aggregation with LISTAGG then it's tricky'.

Well, that is exactly what I need. :(

Upvotes: 2

Views: 1236

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Would this do?

SQL> with test (customer, product, amount) as
  2    (select 'a', 'table', 500 from dual union all
  3     select 'a', 'table', 300 from dual union all
  4     select 'a', 'chair', 100 from dual union all
  5     select 'b', 'rug'  , 50  from dual union all
  6     select 'b', 'chair', 200 from dual
  7    )
  8  select customer,
  9    listagg (product, ', ') within group (order by null) product,
 10    sum(sum_amount) amount
 11  from (select customer, product, sum(amount) sum_amount
 12        from test
 13        group by customer, product
 14       )
 15  group by customer
 16  order by customer;

C PRODUCT                  AMOUNT
- -------------------- ----------
a chair, table                900
b chair, rug                  250

SQL>

Upvotes: 2

Related Questions