I have the data below, returned from a PostgreSQL table using this SQL: SELECT ila.treelevel, ila.app, ila.lrflag, ila.ic, ila.price, ila.treelevel-1 as parent, ila.seq FROM indexlistapp ila WHERE ila.indexlistid IN (SELECT il.indexlistid FROM indexlist il WHERE il.model = '$model' AND ('$year' BETWEEN il.beginyear AND il.endyear) AND il.type = '$part') ORDER BY ila.seq;
Data Returned level app lrflag ic price parent seq indexlistid --------------------------------------------------------------------------------- 1, 'Front', null, null, null, 0, 27, 439755 2, 'V-Series' null, null, null, 1, 28, 439755 3, 'opt J56', null, null, null, 2, 29, 439755 4, 'R.', 'R', '536-01132AR','693.00', 3, 30, 439755 4, 'L.', 'L', '536-01133AL','693.00', 3, 31, 439755 3, 'opt J63', null, null, null, 2, 32, 439755 4, 'R.', 'R', '536-01130R', null, 3, 33, 439755 4, 'L.', 'L', '536-01131L', null, 3, 34, 439755 2, 'exc. V-Series', null, null, null, 1, 35, 439755 3, 'opt JE5', null, null, null, 2, 36, 439755 4, 'AWD', null, null, null, 3, 37, 439755 5, 'R.', null, '536-01142', null, 4, 38, 439755 5, 'L.', null, '536-01143', null, 4, 39, 439755 4, 'RWD', null, null, null, 3, 40, 439755 5, 'R.', null, '536-01143', null, 4, 41, 439755 5, 'L.', null, '536-01142', null, 4, 42, 439755 3, 'opt J55', null, null, null, 2, 43, 439755 4, 'AWD', null, null, null, 3, 44, 439755 5, 'R.', null, '536-01036', null, 4, 45, 439755 5, 'L.', null, '536-01037', null, 4, 46, 439755 4, 'RWD', null, null, null, 3, 47, 439755 5, 'R.', null, '536-01037', null, 4, 48, 439755 5, 'L.', null, '536-01036', null, 4, 49, 439755 1, 'Rear', null, null, null, 0, 260, 439765 2, 'Base', null, null, null, 1, 261, 439765 3, 'opt JE5', null, null, null, 2, 262, 439765 4, 'R.', 'R', '536-01038R', null, 3, 263, 439765 4, 'L.', 'L', '536-01039L', null, 3, 264, 439765 3, 'opt J55', null, null, null, 2, 265, 439765 4, 'R.', 'R', '536-01042R', null, 3, 266, 439765 4, 'L.', 'L', '536-01043L', null, 3, 267, 439765 2, 'V-Series', null, null, null, 1, 268, 439765 3, 'R.', 'R', '536-01134AR', '403.00', 2, 269, 439765 3, 'L.', 'L', '536-01135AL', '466.00', 2, 270, 439765 matching data from indexlist model type beginyear endyear indexlistid --------------------------------------------- 'CTS', '536', 2009, 2010, 439755 'CTS', '536', 2009, 2010, 439765 There are primary keys on indexlist (on indexlistid) and indexlistapp (on indexlistid) but there is no foreign key pointing to the other table. The indexlistid in indexlist points directly to the indexlistid in indexlistapp. The parent column is simply calculated from the treelevel. The tree is built entirely from the seq and treelevel. *I need the data to be returned in this format:* app price ic --------------------------------------------------------------- 'Front-V-Series-opt J56-R.', '$693', '536-01132AR' 'Front-V-Series-opt J56-L.', '$693', '536-01132AL' 'Front-V-Series-opt J63-R.', null, '536-01130R' 'Front-V-Series-opt J63-L.', null, '536-01131L' 'Front-exc. V-Series-opt JE5-AWD-R.', null, '536-01142' 'Front-exc. V-Series-opt JE5-AWD-L.', null, '536-01143' 'Front-exc. V-Series-opt JE5-RWD-R.', null, '536-01143' 'Front-exc. V-Series-opt JE5-RWD-L.', null, '536-01142' 'Front-exc. V-Series-opt J55-AWD-R.', null, '536-01136' 'Front-exc. V-Series-opt J55-AWD-L.', null, '536-01137' 'Front-exc. V-Series-opt J55-RWD-R.', null, '536-01137' 'Front-exc. V-Series-opt J55-RWD-L.', null, '536-01136' 'Rear-Base-opt JE5-R.', null, '536-01038R' 'Rear-Base-opt JE5-L.', null, '536-01039L' 'Rear-Base-opt J55-R.', null, '536-01042R' 'Rear-Base-opt J55-L.', null, '536-01043L' 'Rear-V-Series-R.', '$403.00', '536-01134AR' 'Rear-V-Series-L.', '$466.00', '536-01135AL' I am unsure how to do this in SQL. Not sure why posting the structures is going to help but here they are: *I'm getting close...* Using the string_agg function I came up with this: SELECT indexlistid, treelevel, string_agg(application, ', ' order by seqnbr)::TEXT, string_agg(DISTINCT price::TEXT, ', ') FROM hollander_ic.indexlistapp g WHERE g.indexlistid in (SELECT il.indexlistid FROM hollander_ic.indexlist il WHERE il.modelnm = 'CTS' AND ('2010' BETWEEN il.beginyear AND il.endyear) AND il.parttype = '536') GROUP BY indexlistid, treelevel It returns this data, but I cannot get the ic data: indexlistid treelevel app price ----------------------------------------------------------------------------- 439755, 1, 'Front', '' 439755, 2, 'V-Series, exc. V-Series', '' 439755, 3, 'opt J56, opt J63, opt JE5, opt J55', '' 439755, 4, 'R., L., R., L., AWD, RWD, AWD, RWD', '693.00' 439755, 5, 'R., L., R., L., R., L., R., L.', '' 439765, 1, 'Rear', '' 439765, 2, 'Base, V-Series', '' 439765, 3, 'opt JE5, opt J55, R., L.', '403.00, 466.00' 439765, 4, 'R., L., R., L.', '' Also, using this recursive sql (REF: https://stackoverflow.com/questions/26280379/how-to-concatenate-field-values-with-recursive-query-in-postgresql), I'm able to get it fairly close. Just not sure why it is returning 476 rows vs. 34. http://sqlfiddle.com/#!15/ca1ee/3 WITH RECURSIVE the_tree AS ( SELECT g.seq, g.app::TEXT, g.price, g.ic, g.treelevel::INTEGER FROM indexlistapp g WHERE g.indexlistid in (SELECT il.indexlistid FROM indexlist il WHERE il.model = 'CTS' AND ('2010' BETWEEN il.beginyear AND il.endyear) AND il.type = '536') AND g.treelevel = 1 UNION SELECT t.seq, t.app || ', ' || g.app AS app, t.price, t.ic, t.treelevel::INTEGER + 1 FROM the_tree AS t INNER JOIN indexlistapp g ON g.treelevel = t.treelevel + 1 WHERE g.indexlistid in (SELECT il.indexlistid FROM indexlist il WHERE il.model = 'CTS' AND ('2010' BETWEEN il.beginyear AND il.endyear) AND il.type = '536') ) SELECT * from the_tree; Can anyone help me out here?