11

I need connect all columns (unknown columns) from every rows to one string

I want do sth like this:

select concat(*) from table;

The problem is that the function concat doesn't take parameter "*"

Anyone can help me?

Example:

Table

GID | Name
----------
1   | nameA
2   | nameB
3   | nameC

I need on output:

1nameA
2nameB
3nameC
klin
  • 112,967
  • 15
  • 204
  • 232
k2n
  • 113
  • 1
  • 5

3 Answers3

14
select rtrim(ltrim(replace(tablename::text, ',', ''), '('), ')') from tablename;
klin
  • 112,967
  • 15
  • 204
  • 232
12

I would propose two options. They both work using table_row to text technique.

  1. Quick and dirty:

    select r::text from some_table AS r
    

    Sample output:

    (289,310,,2010-09-10,6,0,1,6,0,30514,6,882,8,4,1,7,2,2,3,1,2,2,2,1,2,2,2,,1,51,0,0,0,0,0,1386,1,1,,6,,0,,,010100002082080000B3EA73156DA25C411E85EB61CB155641)
    
  2. Quick with a possibility to manipulate the data:

    select translate(string_to_array(r::text, ',')::text, '()', '')::text[] from some_table AS r
    

    which returns an actual array of text (text[]) on which any array function can be applied :)
    Sample output (note starting and ending parenthesis type):

    {289,310,"",2010-09-10,6,0,1,6,0,30514,6,882,8,4,1,7,2,2,3,1,2,2,2,1,2,2,2,"",1,51,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1386,1,1,"",6,"",0,"","",010100002082080000B3EA73156DA25C411E85EB61CB155641}
    

Using the second approach and array_to_string(array, delimiter) function you can concatenate all column textual representations into one string. Just pick a delimiter (e.g. ',', '|' or even ''). For example using '|' you end up with a query:

select array_to_string(translate(string_to_array(r::text, ',')::text, '()', '')::text[], '|') from some_table AS r

with sample output:

289|310||2010-09-10|6|0|1|6|0|30514|6|882|8|4|1|7|2|2|3|1|2|2|2|1|2|2|2||1|51|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|2|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1386|1|1||6||0|||010100002082080000B3EA73156DA25C411E85EB61CB155641

Hope that helps :)

andr
  • 15,970
  • 10
  • 45
  • 59
0

Try select concat(gid, user) from foo;, as in this fiddle.

hd1
  • 33,938
  • 5
  • 80
  • 91
  • I don't know the columns. I want to use this solution in procedure in for statement. So I can't use as parameters names of the columns. – k2n Jan 21 '13 at 04:27