On Thu, Jun 25, 2009 at 4:21 PM, Andrew Ballard<[email protected]> wrote:
> On Thu, Jun 25, 2009 at 3:20 PM, Matt Giddings<[email protected]> 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
