Nia Nia - 6 months ago 9
SQL Question

Looping over a table and parsing data of a column

I have a table,

incassoid Amount Details
========= ====== ===============
1 1.0000 5||0.4999;7||0.0001;9||0.0500
2 2.0000 3||1.0000;15||1.0000
3 1.0000 8||1.0000


where in detail column,
5||0.4999
means
5
is a
productid
and
0.4999
is the money product has,what I need is to get the values from the details section and represent it in the table, It needs to loop over all the
incassoid
, and for each
incassoid
, I need the details like this, As an example for
incassoid
1
, it should show its detail like this,

incassoid Productid productamount amount
========= ========= ============ =========
1 5 0.4999 1
1 7 0.0001 1
1 9 0.0500 1


I am trying to find a way to parse the details section, but I am not sure how should I do this, Can some one please help me out in this.

Thanks!!!

Answer

I suppose you have a table of products

Table products

  id
------
    3
    5
    7
    8
    9
   15

You can use that request to do what you want

select t.incassoid, p.id productid,
       substring_index(substring_index(t.details, concat(p.id, '||'), -1), ';', 1) productamount,
       t.amount
from products p, tablename t
where find_in_set(p.id, (select group_concat(p2.id)
                         from products p2, tablename t2
                         where t2.incassoid = t.incassoid
                           and (t2.details like concat(p2.id, '||%')
                                or t2.details like concat('%;', p2.id, '||%'))))

The subrequest in the find_in_set extract a list of product ids from details. Then, for each details (of tablename), the find_in_set filter is used to join the correct list of products.

After that we just have to extract the amount of product in details.

SQLFiddle : http://sqlfiddle.com/#!9/7dfd4/1/0