0

I want to accomplish the following: 

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


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

But I can't use stuff STUFF(( because my system doesn't support it. This link doesn't really help. I have tried everything so far, but ran out of ideas. Thanks

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

Thought about this, but doest' work. Please help:

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
Community
  • 1
  • 1
Will
  • 49
  • 6
  • Postgres doesn't support `stuff`. Here's the equivalent: http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent – sgeddes Jun 02 '16 at 17:03
  • yeah, thank you but those functions don't work either. ERROR: function string_agg(character varying, "unknown") does not exist – Will Jun 02 '16 at 17:12
  • Are there only 3 values in all of your rows? – Matt Jun 02 '16 at 17:17
  • No, there are more than 3, up to 70 values. – Will Jun 02 '16 at 17:19
  • Can you confirm which database/version you are using? Your tags suggest Postgres, but I am not 100% sure if that was your intent. – sstan Jun 02 '16 at 18:02
  • 1
    I am using an mpp database. http://www.actian.com/products/big-data-analytics-platforms-with-hadoop/matrix-mpp-analytics-databases/ – Will Jun 02 '16 at 18:04

0 Answers0