String concatenation with GROUP BY

Question

Using Postgres, I want to convert the following records:

ID  serviceaccess
11  Value1
11  Value2
11  Value3
22  Value2
22  Value1
22  Value1

Into this result:

ID
11 value1, value2, value3
22 value2, value1, value1

I can't use functions, because the system I am using doesn't support those. I could do case statements though.

I also checked the following:

SQL Server : GROUP BY clause to get comma-separated values

I tried this, but it doesn't work:

WITH RecurRanked ( dbid, rnk, serviceaccess)
             AS ( 
                SELECT t.t1.dbid, t.t1.rnk, t.t2.serviceaccess || ', ' || t.t1.serviceaccess 
                FROM t.t1
                INNER JOIN t.t2  ON t.t1.dbid = RecurRanked.dbid AND t.t1.rnk = RecurRanked.rnk + 1)
SELECT dbid, MAX(serviceaccess)
FROM RecurRanked
GROUP BY dbid;


    SELECT t.t1.dbid, t.t1.rnk, t.t2.serviceaccess || ', ' || t.t1.serviceaccess 
                FROM t.t1
                INNER JOIN t.t2  ON t.t1.dbid = t.t2.dbid AND t.t1.rnk = t.t2.rnk + 1

In PostgreSQL 9.0, PostgreSQL introduced the STRING_AGG. You can look it up here: http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL,-SQL-Server,-and-MySQL.html — vercelli, Jun 06 '16 at 17:14
Please tell us which "system" you are using to access the database so that we can help with a solution that fits. Being unable to use a function in a query seems like a big problem and I wouldn't use an ORM that had that restriction. That being said, we may be able to help find a workaround. — Robin Daugherty, Jun 06 '16 at 18:20

score 1 · Answer 1 · answered Jun 06 '16 at 17:16

I don't fully understand what you mean by "But I can't use functions, because the system, I am using doesn't support those. I am using postgres SQL though".

You can use the string_agg aggregate function in PostgreSQL.

select ID, string_agg(serviceaccess, ',') group by ID;

strobering · Answer 2 · 2016-06-10T22:25:56.270

It's really hard to do what you want using pure SQL. It could be useful the following (which is not the perfect solution):

(e.g.) to convert the following records:

id| serviceaccess
-----------------------
11|" "
11|"Value1"
11|"Value2"
22|" "
22|"Value1"
22|"Value2"
22|"Value3"

Tested in postgresql. Unfortunately it can not be supported on the DBMS you are using:

SELECT t1.id, (max( t1.serviceaccess ||  ',') ||
               max( t2.serviceaccess ||  ',') ||
               max( t3.serviceaccess ||  ',') ||
               max( t4.serviceaccess ||  ',') ||
               max( t5.serviceaccess)) as Services
FROM       test as t1
INNER JOIN test as t2 ON t1.id =t2.id AND (t2.serviceaccess > t1.serviceaccess or 
t1.serviceaccess = ' ')
INNER JOIN test as t3 ON t2.id =t3.id AND (t3.serviceaccess > t2.serviceaccess or 
t2.serviceaccess = ' ')
INNER JOIN test as t4 ON t3.id =t4.id AND (t4.serviceaccess > t3.serviceaccess or 
t3.serviceaccess = ' ')
INNER JOIN test as t5 ON t4.id =t5.id AND (t5.serviceaccess > t4.serviceaccess or 
t4.serviceaccess = ' ')
GROUP BY t1.id

Result:

id| services
------------------------------
22| " , ,Value1,Value2,Value3"
11| " , , ,Value1,Value2"

kind regards!

String concatenation with GROUP BY

2 Answers2