Ummm yeah, its going to take me a while to wrap my head around that one.
On Thu, Jun 25, 2009 at 5:07 PM, Andrew Ballard <aball...@gmail.com> wrote:
> On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard<aball...@gmail.com> wrote:
> > 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
> >> 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,
> > 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:
Information Technology Services
Saginaw Valley State University