Ummm yeah, its going to take me a while to wrap my head around that one.
thanks though!
Matt
On Thu, Jun 25, 2009 at 5:07 PM, Andrew Ballard <aballard@xxxxxxxxx> wrote:
> On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard<aballard@xxxxxxxxx> wrote:
> > On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddings<mcgiddin@xxxxxxxx> 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
>
--
Matt Giddings
Web Programmer
Information Technology Services
Saginaw Valley State University
Phone: 989.964.7247
http://www.svsu.edu
