Re: OT mysql pivot table problem
| From: | Andrew Ballard | Date: | Thu, 25 Jun 2009 20:21:39 +0000 |
| Subject: | Re: OT mysql pivot table problem | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-294613@lists.php.net to get a copy of this message | ||
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