Query to return column values as comma separated - postgresql

Question

I am using materialized view in my application.

The query is as follows

SELECT
  DISTINCT n.order_id,
  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
  pt.notes n
 WHERE
 n.order_id = 891090 AND
  (n.customer_billing = TRUE or n.commander = TRUE or n.agent = TRUE)

This produces the following output

Instead of two records, the output of finance column should be comma separated values.

That is the output should be

AP (Agent), AR (Customer Billing)

I know aggregate functions can be used here, but not sure how to use when the query has switch cases and all.

Any idea on how to achieve this?

lookup use of string_Agg() I don't recommend denormalizing data like this however. it's more of a UI Function. However there are some special circumstances where you may need to... — xQbert
– xQbert, Commented Feb 23, 2022 at 16:19
This is close to a duplicate: stackoverflow.com/questions/43870/… — xQbert
– xQbert, Commented Feb 23, 2022 at 16:44

xQbert · Accepted Answer · 2022-02-23 16:42:02Z

2

Consider: Seldom is it wise to de-normalize data this way in the database query; Better done in UI. However, if this is going directly to an aggregated report; it may make sense to do it here... But keep in mind if this ever will need to be separated later; you're better off doing the combine in the UI.

Notes:

Wrap case in string_Agg() function the "Case" statement is then wrapped in the string_agg() function and since we operate inside out just like math, the case is resolved 1st.
added GROUP BY for non-aggregated element(s)
eliminated DISTINCT as GROUP BY takes care of it so distinct is unneeded clutter.
added additional sample data to show how multiple columns handled.
eliminated pt.notes table as I'm not using it in my generated common table expression
You will not need the "with notes as ()..." Just the select after it and correct the FROM clause to yours

dbfiddle.uk Example

More on this-->How to concatenate string of a string field in postgresql

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

SELECT
  n.order_id,
  string_Agg(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 = 891090 AND
  (n.customer_billing = TRUE or n.commander = TRUE or n.agent = TRUE)
 GROUP BY ORDER_ID

Giving us:

+----------+---------------------------------------+
| order_id |                finance                |
+----------+---------------------------------------+
|   891091 | AP (Commander), AR (Customer Billing) |
|   891090 | AP (Agent), AR (Customer Billing)     |
+----------+---------------------------------------+

edited Feb 23, 2022 at 16:42

answered Feb 23, 2022 at 16:22

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

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

3 Comments

prajeesh Over a year ago

This can have duplicate values in the comma separated string like "AP (Commander), AR (Customer Billing), AR (Customer Billing)". Is there a way to eliminate the duplicate from the current output?

xQbert Over a year ago

do you have sample data showing this problem for me? I think distinct may work but I need to understand the problem: stackoverflow.com/questions/24785084/… String_Agg(Distinct case...)

xQbert Over a year ago

Keep in mind we're starting to deal with edge cases that were not defined in the orginal scope of the question. Other such things may pop-up!

Collectives™ on Stack Overflow

Query to return column values as comma separated - postgresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related