On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddings<mcgid...@svsu.edu> wrote: > I know this is the off topic (sorry), but it is a php project that I'm > working on! I need some pointers on how to pivot a mysql column (containing > comma delimited data) into an equal number of rows (see example). Any > direction (pointers to links, etc. would be appreciated). > > From this: > > user.table > uid|name|groups > 1|mcgiddin|1,4,7,10,12 > > > To this: > > pivot.table > uid|group > 1|1 > 1|4 > 1|7 > 1|10 > 1|12 >
The best performance I've seen for a SQL solution uses a tally table. CREATE TABLE Tally ( N int not null, PRIMARY KEY (N) ) The table holds a sequence of numbers from 1 to some large number. Then you can write a query something like this: SELECT uid, SUBSTRING(user.table.groups,N+1,LOCATE(',',user.table.groups,N+1)-N-1) FROM Tally, user.table WHERE N < LENGTH(user.table.groups) AND SUBSTRING(user.table.groups,N,1) = ',' Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php