0

I have table that stores comma separated strings - I've created a solution via PHP that can separate the array and display the data as required; however, I now have the need to do this strictly by SQL query.

So here is what I start with: ID | TIME | CODES

1 | '2','','','8','','','2','','','2','','','4','','','3','','' | '102','104','105','106','107','108'
2 | '4','','','4','','','8','','','2','','','2','','' | '105','106','107','114','401'

every 3 values in the time belong to the code. So I need to combine everything into a single table to run some sums by category.

Something along the lines of this for an output:

ID  |  RT  |  PT  |  OT  |  CODE
---------------------------------
1  |  2  |  NULL  |  NULL  |  102
1  |  8  |  NULL  |  NULL  |  104
1  |  2  |  NULL  |  NULL  |  105
1  |  2  |  NULL  |  NULL  |  106
1  |  4  |  NULL  |  NULL  |  107
1  |  3  |  NULL  |  NULL  |  108
2  |  4  |  NULL  |  NULL  |  105
2  |  4  |  NULL  |  NULL  |  106
2  |  8  |  NULL  |  NULL  |  107
2  |  2  |  NULL  |  NULL  |  114
2  |  2  |  NULL  |  NULL  |  401

NULL, blank or 0 will work where nothing was provided ('')

Cœur
  • 37,241
  • 25
  • 195
  • 267
wildwally
  • 39
  • 1
  • 6
  • Just to confirm: each row in your table consists of three fields - ID, TIME, and CODES. There is one ID, then multiple (3xN) TIMES (in groups of three), and then N CODES. The number of codes is always N, but N varies per ID. Is it ever possible that an ID has N=0 records? – Floris Jul 11 '13 at 20:59
  • You may find the answer given [here](http://stackoverflow.com/a/2703/1967396) quite helpful. It's not the complete solution, but a good start I think. – Floris Jul 11 '13 at 21:06
  • correct, and no there will never be a N-0 records – wildwally Jul 11 '13 at 21:06
  • That solution works great for spaces - but when you try to modify for comma its a whole different set of problems. – wildwally Jul 11 '13 at 22:15
  • Why is that - can't you just set the separator (second parameter) to `','`? Perhaps if you update your question with "here is what I tried, and how it didn't work for me", it will trigger some more interest (if only because editing your question "puts it back on top of the queue"). – Floris Jul 11 '13 at 22:45

0 Answers0