Thread: Concatenate of values in hierarchical data
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?
> On 2 Apr 2018, at 19:23, Mr. Baseball 34 <mrbaseball34@gmail.com> wrote:
>
> 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
pointingto the other table. The indexlistid in indexlist
> points directly to the indexlistid in indexlistapp. The parent column is simply calculated from the treelevel. The
treeis 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.
(…)
> Also, using this recursive sql
(REF:https://stackoverflow.com/questions/26280379/how-to-concatenate-field-values-with-recursive-query-in-postgresql),
I'mable 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?
Your JOIN in the recursive part of the CTE should probably be ON g.parent = t.treelevel AND g.indexlistid =
t.indexlistid.Since both the initial and recursive part use the same value for indexlistid, the subquery in the
recursivepart is superfluous.
You should also take the order in seq into account for items on the same node, if that is possible in your data,
otherwisethere is no guarantee that you'll get your string components in the correct order.
I think you need something closer to this:
WITH RECURSIVE the_tree AS (
SELECT g.seq, g.app::TEXT, g.price, g.ic, g.treelevel::INTEGER, g.indexlistid
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.parent = 0
UNION ALL
SELECT t.seq, t.app || ', ' || g.app AS app, t.price, t.ic, g.treelevel, g.indexlistid
FROM the_tree t
INNER JOIN indexlistapp g ON g.parent = t.treelevel AND g.indexlistid = t.indexlistid
ORDER BY g.seq
)
SELECT * FROM the_tree
Another observation is that you only seem interested in the leaf nodes of your tree. Recognising leaf nodes in your
hierarchicalquery can be achieved by adding a column that tests the existence of a node with the current node as its
parent- if it does not exist, this node is a leaf node.
The result of SELECT * FROM the_tree can then be filtered to return leaf nodes only.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.