Roy Harrell <1600uVision@stripped> wrote on 10/04/2005
03:15:33 PM:
> Suppose I have a simple table as follows:
>
> PartName Tolerance Cycles
> A 1 10
> A 2 11
> A 3 13
> A 4 15
> A 5 18
> B 1 12
> B 2 14
> B 3 16
> B 4 16
> B 5 17
> C 1 6
> C 2 7
> C 3 7
> C 4 8
> C 5 10
>
>
> How do I set up a query whose output would
> look like this:
>
> Tolerance PartA PartB PartC
> 1 10 12 6
> 2 11 14 7
> 3 13 16 7
> 4 15 16 8
> 5 18 17 10
>
>
> Thanks,
>
> Roy Harrell
> Adaptive Equipment
> 352.372.7821
>
What you are doing is called a Pivot Table or a Crosstab Query. In order
to do one with MySQL, you need to decide what kind of information you want
in your "middle cells". That's because you need to pick an aggregate
function (sum, min, max, avg, etc.) to wrap around your middle cell data.
I will break it down by min cycles, max cycles, and avg cycles for each of
the parts A, B, and C.
SELECT
Tolerance
, MIN(if(PartName='A', cycles, NULL)) as A_min
, MAX(if(PartName='A', cycles, NULL)) as A_max
, AVG(if(PartName='A', cycles, NULL)) as A_avg
, MIN(if(PartName='B', cycles, NULL)) as B_min
, MAX(if(PartName='B', cycles, NULL)) as B_max
, AVG(if(PartName='B', cycles, NULL)) as B_avg
, MIN(if(PartName='C', cycles, NULL)) as C_min
, MAX(if(PartName='C', cycles, NULL)) as C_max
, AVG(if(PartName='C', cycles, NULL)) as C_avg
FROM simpletable
GROUP BY Tolerance;
There is no "automatic" solution for this with MySQL. You have to know
your what your analysis columns will be and how to identify them before
you can write this query. Nothing says you can't use code to write the
query (notice the repetitive pattern?) but there is no native function to
do that for you.
Notes: When computing MIN(), MAX(), or AVG(), all NULL values should be
ignored. That's what the IF() clause is for, to pick out just the values
you want to analyze.
Does that help?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
