By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

concatenate duplicate

P: 2
I've a table of cust ids where one customer can have multiple services (e.g. svc1, svc2, svc3). One cust id record contains one servicename field. So there can be duplicate cust records.

custid servicename
------ -----------
1 svc1
1 svc2
2 svc3
2 svc4
3 svc1
3 svc5
4 svc1
4 svc2
4 svc3

Desired output:
custid servicename
------ -----------
1 svc1:svc2
2 svc3:svc4
3 svc1:svc5
4 svc1:svc2:svc3

How do I concatenate the servicename field of these duplicate records into one field?

Appreciate your help. Thanks
Aug 24 '10 #1
Share this Question
Share on Google+
2 Replies

P: 2
I also tried the following but it seems to run forever:

SELECT DISTINCT ON (custid) custid, array_to_string(array( SELECT b.servicename FROM table1 b WHERE b.custid = a.custid AND b.servicename != a.servicename ), ':') AS svc_combi
FROM table1 a
GROUP BY custid, svc_combi;

Any faster workaround appreciated. Thanks in advance.
Aug 25 '10 #2

Expert 100+
P: 700
which postgres version do you use?
Sep 1 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.