Using if statement in string_agg function - postreSQL

Question

The query is as follows

WITH notes AS (
    SELECT 891090 Order_ID, False customer_billing,  false commander, true agent 
    UNION ALL
    SELECT 891091, false, true, true 
    UNION ALL
    SELECT 891091, true, false, false)

SELECT
  n.order_id,
  string_Agg(distinct CASE
    WHEN n.customer_billing = TRUE THEN 'AR (Customer Billing)'
    WHEN n.commander = TRUE THEN 'AP (Commander)'
    WHEN n.agent = TRUE THEN 'AP (Agent)'
    ELSE NULL
  END,', ') AS finance
FROM notes n
WHERE
 n.order_id = 891091 AND (n.customer_billing = TRUE or n.commander = TRUE or n.agent = TRUE)
 GROUP BY ORDER_ID

As you can see there are two records with order_id as 891091.

First 891091 record has commander and agent set as true
Second 891091 record has customer_billing set as true

Since switch case is used, it considers only the first true value and returns commander and does not consider agent.

So the output becomes

order_id    finance
891091  AP (Commander), AR (Customer Billing)

dbfiddle.uk Example

I need all the true values in the record to be considered so that the output becomes

order_id    finance
    891091  AP (Commander), AP (Agent), AR (Customer Billing)

My initial thought is that using if statement instead of case statement may fix this. I am not sure how to do this inside string_agg function

How to achieve this?

EDIT 1:

The answer specified below works almost fine. But the issue is that the comma separated values are not distinct

Here is the updated fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9647d92870e3944516172eda83a8ac6e

mysql or postgresql, thats the question .... (Please delete the tag that is not correct!) — Luuk
– Luuk, Commented Feb 25, 2022 at 14:02
What dbms did you use? your dbfiddle use Postgres but your title wrote mysql — D-Shih
– D-Shih, Commented Feb 25, 2022 at 14:02
For 1 record customer_billing is true => 'AR (Customer Billing)' is added, for another record commander is true => 'AP (Commander)' is added. Maybe rea about PostgreSQL SELECT DISTINCT — Luuk
– Luuk, Commented Feb 25, 2022 at 14:07
SELECT 891091, false, true, true . Here commander and agent are true. — prajeesh
– prajeesh, Commented Feb 25, 2022 at 14:08

Andronicus · Accepted Answer · 2022-02-25 14:57:23Z

2

You can consider splitting your case into separate ones and using array to collect them. Then you can use array_to_string to format:

WITH notes AS (
  SELECT 891090 Order_ID, False customer_billing,  false commander, true agent UNION ALL
  SELECT 891091, false, true, true UNION ALL
  SELECT 891091, true, true, false),
tmp as (
SELECT
  n.order_id id,
      array_agg(
        ARRAY[
          CASE WHEN n.customer_billing = TRUE THEN 'AR (Customer Billing)' END,
          CASE WHEN n.commander = TRUE THEN 'AP (Commander)' END,
          CASE WHEN n.agent = TRUE THEN 'AP (Agent)' END
        ]) AS finance_array
FROM notes n
WHERE
 n.order_id = 891091 AND (n.customer_billing = TRUE or n.commander = TRUE or n.agent = TRUE)
 GROUP BY ORDER_ID )
 select id, array_to_string(array(select distinct e from unnest(finance_array) as a(e)), ', ')
 from tmp;

Here is db_fiddle.

edited Feb 25, 2022 at 14:57

answered Feb 25, 2022 at 14:10

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prajeesh Over a year ago

This works almost correct. The issue here is that the comma separated values are not distinct. dbfiddle.uk/…

Andronicus Over a year ago

@prajeesh you're totally correct, I forgot about that case. I have corrected the answer, please have a look at the snippet

Collectives™ on Stack Overflow

Using if statement in string_agg function - postreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related