convert rows to string in postgresql

Question

Table name is t1. Field name is name1. name1 has value like this

-------------------------------------------+
+       name1                              +
--------------------------------------------
|    "a_2013,AcMaster,Master"              |
|   "b_2014,AcMaster,Master"               |
|   "c_2013,a_AcMaster,a_Master"           |
|    "d_2014,a_AcMaster,a_Master"          |
|__________________________________________|

But i want to get reslut like this

  master          acmaster               text
   Master          AcMaster                a_2013,b_2014
   a_Master        a_Master                c_2013,d_2014

So i try like this

    select (string_to_array(schemaname,',')) [3] as master,(string_to_array(schemaname,','))
 [2] as acmaster,(string_to_array(schemaname,',')) [1] from appsetup.company2 
c2,appsetup.company1 c1,appsetup.companygroup cg where    c1.compno=c2.compno and 
cg.compgroupno=c1.compgroupno and c1.compno in (3,2) group by 
string_to_array(schemaname,',')) [3],
(string_to_array(schemaname,',')) [2],cg.compgroupno,schemaname order by 
cg.compgroupno

But its return

master          acmaster               text
   Master          AcMaster              ["a_2013","b_2014"]
   a_Master        a_Master              ["c_2013","d_2014"]

How to get my wanted result?

Am using Postgresql 9.3

Erwin Brandstetter · Accepted Answer · 2014-02-04 08:35:08Z

41

You can try this:

select string_agg(name1, ',') as Name1s
from t1

string_agg

edited Feb 4, 2014 at 8:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Feb 3, 2014 at 11:33

Krishnraj Rana

6,6483 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grengas Over a year ago

In case your column type to aggregate is not text, you need to add type cast: select string_agg(name1::text, ',') as Name1s from t1

Collectives™ on Stack Overflow

convert rows to string in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related