1

I am using postgresql 9.0 I am wonder if its possible to concatenate three attributes together.

this is how I concatenate two attributes (book & the comma):

SELECT string_agg(book, ',') FROM authors where id = 1;


| book1,book2,book3|
--------------------

how can I do something like below:

SELECT string_agg(name, ':', book, ',') FROM authors where id = 1;

| Ahmad: book1,book2,book3|
  ----------------

can some one help? thanks.

tokhi
  • 21,044
  • 23
  • 95
  • 105
  • "this is how I concatenate two attributes" - that looks to me like concatenating all the values of one field, not two fields. – IMSoP Oct 07 '14 at 10:32

2 Answers2

5

Just concatenate the fields like this:

SELECT name || ':' || string_agg(book, ',') FROM authors where id = 1;

Edit:

If your SQL returns multiple names you need to group by name (if you have multiple authors with the same name it gets a bit more complicated. I won't cover that case in this answer):

SELECT name || ':' || string_agg(book, ',') 
  FROM authors where id = 1 
 GROUP BY name;

If you want the books in alphabetical order you can add an ORDER BY for the books:

SELECT name || ':' || string_agg(book, ',') WITHIN GROUP ORDER BY book 
  FROM authors where id = 1 
 GROUP BY name;
Klas Lindbäck
  • 33,105
  • 5
  • 57
  • 82
1
SELECT name || ': ' || string_agg(book, ',') FROM authors where id = 1 group by name ;
Vivek S.
  • 19,945
  • 7
  • 68
  • 85