CODEWITHSUNDEEP
sqlpostgresql
prajeesh
prajeesh

Reputation: 2382

Query to return column values as comma separated - postgresql

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

enter image description here

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?

Upvotes: 0

Views: 2386

Answers (1)

xQbert
xQbert

Reputation: 35323

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

Upvotes: 2

Related Questions