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, Feb 23 '22 at 16:19
This is close to a duplicate: https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query — xQbert, Feb 23 '22 at 16:44

xQbert · Accepted Answer · 2022-02-23T16:42:02.260

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)     |
+----------+---------------------------------------+

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? — prajeesh, Feb 23 '22 at 17:17
do you have sample data showing this problem for me? I think distinct may work but I need to understand the problem: https://stackoverflow.com/questions/24785084/issue-with-string-agg-with-distinct-in-postgres `String_Agg(Distinct case...)` — xQbert, Feb 23 '22 at 17:22
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! — xQbert, Feb 23 '22 at 17:49

Query to return column values as comma separated - postgresql

1 Answers1