Re: OT mysql pivot table problem

On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard<aballard@gmail.com> wrote: > On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddings<mcgiddin@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 > OK, I actually tested this and it appears I missed something. For this query to work, the string has to begin and end with the delimiter. So I just replaced user.table.groups with CONCAT(',', user.table.groups, ','). SELECT uid, SUBSTRING(CONCAT(',', user.table.groups, ','),N+1,LOCATE(',',CONCAT(',', user.table.groups, ','),N+1)-N-1) FROM Tally, sample WHERE N < LENGTH(CONCAT(',', user.table.groups, ',')) AND SUBSTRING(CONCAT(',', user.table.groups, ','),N,1) = ',' For more in-depth information, check out some of the links in this search: http://www.google.com/search?q=sql+split+tally+table Andrew