CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
prajeesh
prajeesh

Reputation: 2382

Using if statement in string_agg function - postreSQL

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.

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

Upvotes: 2

Views: 733

Answers (1)

Andronicus
Andronicus

Reputation: 26046

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.

Upvotes: 2

Related Questions