How to write SQL query to get column wise comma separated value list from record

Question

I am searching for a sql query(I am using Postgres database) that can give all values from a single record in comma separated.

e.g.

I have a table named master_table and having column no,name,phone

SELECT * FROM master_table WHERE id = 1

From above query I want result as

1,piyush,1111111
2,john,2222222

Above table is example only tables are dynamic so column numbers and names cannot be fixed.

Thanks in advance

Please find here, http://stackoverflow.com/questions/14612394/query-for-comma-separated-ids-to-comma-separated-values — Abu Umer, Oct 07 '13 at 10:20

Eggplant · Accepted Answer · 2013-10-07T11:35:25.150

SELECT (column_no::text || ',' || name || ',' || phone::text) AS comma_separated
FROM master_table WHERE id = 1

EDIT :

After your comment, I think you'd need a function. Even if I don't agree with the whole idea (it's much better to do that in the application layer), here is a function which makes what you are asking. It is ugly.

CREATE OR REPLACE FUNCTION GetCommaSeparatedValues(PAR_table text, PAR_where_clause text DEFAULT '') RETURNS TABLE ( 
    comma_separated_values text
) AS $$
DECLARE
    REC_columns record;
    VAR_query text;
BEGIN
    VAR_query := '';
    FOR REC_columns IN SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema AND table_name = PAR_table LOOP
        IF VAR_query <> '' THEN
            VAR_query := VAR_query || ' || '','' || ';
        END IF;
        VAR_query := VAR_query || ' CASE WHEN ' || REC_columns.column_name || ' IS NULL THEN ''null'' ELSE ' || REC_columns.column_name || '::text END';
    END LOOP;
    VAR_query := 'SELECT ' || VAR_query || ' FROM ' || PAR_table::regclass || ' ' || PAR_where_clause;
    RETURN QUERY EXECUTE VAR_query;
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT * FROM GetCommaSeparatedValues('table1');

or

SELECT * FROM GetCommaSeparatedValues('table2', 'WHERE id = 1');

Hello Thanks for your answer but in my case I need this kind of value for different tables by single query. So I cannot decide column numbers or name. I will need something like loop through columns. — compyutech, Oct 07 '13 at 10:37
Please make an example then. You are looking for a query to get a string representation of comma separated values of ALL the fields for each record in any given table? A pl/pgsql function seems the way to go then. — Eggplant, Oct 07 '13 at 10:42

score 2 · Answer 2 · answered Oct 07 '13 at 10:26

2

you can use string_agg to achieve above, for ex :

SELECT string_agg(id, ',') FROM table

Ref : http://www.craigkerstiens.com/2013/04/17/array-agg/

answered Oct 07 '13 at 10:26

Sunil Verma

2,490
1
14
15

Hello string_agg function gives comma separated values from a different records from a single column where I need values from different columns – compyutech Oct 07 '13 at 10:36

score 1 · Answer 3 · answered Oct 07 '13 at 11:52

The problem about doing this directly on a SELECT query is that it lead to some problems, the principal is about escapes. For instance, if you have a comma inside one of the record strings, how would you treat that?

Of course you could parse the strings and escape it, but the most common (and recommended) approach is to use the well-known CSV format. And PostgreSQL already gives you a result that way using the COPY command, so you don't have to reinvent the wheel:

COPY (SELECT * FROM master_table WHERE id = 1) TO stdout WITH CSV;

Curry · Answer 4 · 2013-10-07T12:16:09.013

If you like to get csv formatted data from psql shell, just modify some psql option You can do it using below command

\a: toggle between unaligned and aligned output mode
\f [STRING]: show or set field separator for unaligned query output

also you can save query results by below command

\o [FILE]: send all query results to file or |pipe

Example

postgres=# select * from master_table;
 id | string | number
----+--------+---------
  1 | piyush | 1111111
  2 | john   | 2222222
(2 rows)
postgres=# \a
Output format is unaligned.
postgres=# \f ,
Field separator is ",".
postgres=# select * from master_table;
no,name,phone
1,piyush,1111111
2,john,2222222
(2 rows)
postgres=# \o result.csv
postgres=# select * from master_table; => query result is saved in 'result.csv'
postgres=#

How to write SQL query to get column wise comma separated value list from record

4 Answers4