Concatenate of values in hierarchical data
| От | Mr. Baseball 34 |
|---|---|
| Тема | Concatenate of values in hierarchical data |
| Дата | |
| Msg-id | CAFTsGE_bbx_Xp=maF_CvdF6zZix_hq06eyVnw7PX8Sgd8B2gwA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Concatenate of values in hierarchical data
(Alban Hertroys <haramrae@gmail.com>)
|
| Список | pgsql-general |
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?
В списке pgsql-general по дате отправления:
Предыдущее
От: Rob SargentДата:
Сообщение: Re: Please suggest the best suited unit test frame work forpostgresql database.