Using if statement in string_agg function

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

CodePudding user response：

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.

CodePudding user response：

You can try to use the condition aggregate function in subquery or cte first that can make sure only display once when the value is true multiple times in same columns

Then use VALUES() and JOIN LATERAL combine the string before doing string_Agg

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)
,CTE AS (
   SELECT order_id,
          MAX(CASE WHEN customer_billing = TRUE THEN 'AR (Customer Billing)' END) customer_billing,
          MAX(CASE WHEN commander = TRUE THEN 'AP (Commander)' END) commander,
          MAX(CASE WHEN agent = TRUE THEN 'AP (Agent)' END) agent
   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
  order_id,
  string_Agg(v.values,', ') AS finance
FROM CTE 
JOIN LATERAL(VALUES(customer_billing),(commander),(agent)) v(values) ON TRUE
GROUP BY order_id

sqlfiddle