Postgresql Query to Return Results as a Comma Separated List

PostgreSQL query to return results as a comma separated list

SELECT string_agg(id::text, ',') FROM table

Requires PostgreSQL 9.0 but that's not a problem.

PostgreSQL: output rows as list of comma-separated strings

You seem to want:

SELECT id, CONCAT_WS(',', info, name, message)
FROM myschema.mytable;

No aggregation is necessary. The values you want are all in one row.

You can, of course, include id in the string instead of as a separate column:

SELECT CONCAT_WS(',', id, info, name, message)
FROM myschema.mytable;

Query to return column values as comma separated - postgresql

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

fetch values into a comma separated string in postgresql

Whatever tool you are using just shows you the data like that for convenience. But you can also use the resultset in a subquery, like this

SELECT x FROM table WHERE status in (
    SELECT code_id FROM code WHERE status = 1
)

Postgres to fetch the list having comma separated values

Aggregate by employee and use string_agg:

select
    c.employee_id,         -- or just c.* assuming employee_id is a PK
    string_agg(ce.email, ',') as emails
from root.employee c
full outer join root.employee_email ce
    on c.employee_id = ce.employee_id
group by
    c.employee_id
order by
    c.employee_id
limit 1000
offset 0;

Postreg SQL get All Value with quotes from the comma separated

Try this if you just want a string back with the quotes

WITH sample AS (
  SELECT '1,2,3,4,5'::text as test
)

SELECT
  '''' || array_to_string(
    string_to_array(test, ','),
    ''','''
  ) || ''''
FROM sample

Function to select column values by comparing against comma separated list

You need to use TRIM to strip the " from your ll value:

select unique_code 
from codings 
where unique_code = ANY (regexp_split_to_array(trim(both '"' from '"Java,CPP"'), '\,'));

Output for your sample data:

unique_code
Java
CPP

SQLFiddle demo

You also have an issue in that you are assigning multiple values in one statement, for which you need to use the ARRAY operator. Change

select unique_code into foo from codings where unique_code = ANY(ko);

to

select array(select unique_code from codings where unique_code = ANY(ko)) into foo;

---

Related Topics