How to Convert Comma Seperated Column into rows and add counter

Question

I am having a table with these values

Table : Documents

id  |       document
----|-------------
1   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
2   |   doc.txt 
3   |   doc.txt , doc1.txt 
4   |   doc.txt , doc1.txt , doc2.rtf 
5   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
6   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx 
7   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
8   |   doc.txt , doc1.txt , doc2.rtf 
9   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
10  |   doc.txt , doc1.txt 

SQL FIDDLE SCHEMA

I need result like this. Where id = 5

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf
    4   |   doc3.docx
    5   |   doc4.doc

Where id = 4

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf

You see i need to explode comma seperated column and count how many values are there. I dont like this schema but i am working on an existing project and can not change it. I need counter to display in the user interface. So counter is necessary too. How can i do that? Also i can not do it on the php end because i am using pyrocms and i need to display it using pyrocms tage which does not allow me to use php in the views.

Aceto

Answer 1

Look at this SQL FIDDLE , maybe it is that you want. You must use helper table sequence

SELECT * FROM
(SELECT S.Id Counter,
REPLACE(SUBSTRING(SUBSTRING_INDEX(document, ' , ', S.Id),
       LENGTH(SUBSTRING_INDEX(document, ' , ', S.Id - 1)) + 1),
       ' , ', '') docs
FROM documents D, sequence S
WHERE D.id = 3) A
WHERE A.docs <> ''

Whitfield

Sequence table? – Obstacle

Answer 2

using stored procedures you can solve your problem. here's an exact duplicate of your problem already solved

Planula

Answer 3

I'm afraid there is no easy way to create SQL query there is also no split function available.

What you can do is to create your own function, as described here

You can also process the results directly in php code.

Dunite