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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
