concatenate duplicate

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • beeth9
    New Member
    • Aug 2010
    • 2
    #1

    concatenate duplicate

    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.

    e.g.
    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
    Mike
    Tags: concatenate duplicate
    • beeth9
      New Member
      • Aug 2010
      • 2
      #2
      concatenating columns into a single field

      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.
      Mike

        Comment

        • rski
          Recognized Expert Contributor
          • Dec 2006
          • 700
          #3
          which postgres version do you use?

            Comment

            Previous template Next
            Working...