Other usage = A workaround to "For XML PATH" (see
http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/ )
List of colors of a bag :
**************************
with colors(bag,color) as
(values('bag1','blue'),('bag1','red'),('bag2','green'),('bag2','yellow'),('bag1','yellow'))
, bags (bag, colors, last) as (
select bag , min(color) as colors, min(color) as last from colors
group by bag
union all
select b.bag, colors ||' - ' || color , color from bags b ,
colors l
where b.bag=l.bag and l.color=(
select min(color) from colors as x where b.bag=x.bag and
b.last<x.color)
)
select bag, max(colors) as colors from bags group by bag;
==> gives :
*************
bag colors bag1 blue - red - yellow bag2 green - yellow
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
