CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Will
Will

Reputation: 49

String concatenation with GROUP BY

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

Upvotes: 0

Views: 241

Answers (2)

strobering
strobering

Reputation: 176

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!

Upvotes: 1

uncaught_exception
uncaught_exception

Reputation: 1078

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;

Upvotes: 1

Related Questions