Solved

I would like to create a pivot table using only mysql query

Posted on 2013-01-14
26
1,320 Views
Last Modified: 2013-09-16
Hi all,

Is there a way to create a pivot table in MySQL? using only MySQL?

eg.:
going from this list:

ArtCode      ClrCode      Price
Z8407      1      57,00
Z8407      2      59,00
Z8407      3      56,00
Z8407      4      60,00
Z8407      5      58,00
Z8407      6      55,00
Z8408      1      59,00
Z8408      2      61,00
Z8408      3      58,00
Z8408      4      62,00
Z8408      5      60,00
Z8408      6      57,00
Z8410      1      66,00
Z8410      2      68,00
Z8410      3      65,00
Z8410      4      69,00
Z8410      5      67,00
Z8410      6      64,00
Z8412      1      76,50
Z8412      2      78,50
Z8412      3      75,50
Z8412      4      79,50
Z8412      5      77,50
Z8412      6      74,50
Z8414      1      87,00
Z8414      2      89,00
Z8414      3      86,00
Z8414      4      90,00
Z8414      5      88,00
Z8414      6      85,00
Z8416      1      103,50
Z8416      2      105,50
Z8416      3      102,50
Z8416      4      106,50
Z8416      5      104,50
Z8416      6      101,50





To something like this

             1            2            3            4            5            6
Z8407      57            59            56            60            58            55
Z8408      59            61            58            62            60            57
Z8410      66            68            65            69            67            64
Z8412      76,5            78,5            75,5            79,5            77,5            74,5
Z8414      87            89            86            90            88            85
Z8416      103,5      105,5      102,5      106,5      104,5      101,5
0
Comment
Question by:24Carat
  • 11
  • 10
  • 4
  • +1
26 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 38773657
0
 

Author Comment

by:24Carat
ID: 38773694
In your example bob isn't dynamic,

also the amount of salespersons is 2.

In my case it must also be dynamic it can be 6, but in reallity it is somewhere in between 1 and 35.

Writing 35 lines of code is also not an option because the values are always different .
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38773696
this should do
select ArtCode      
  , max(case when ClrCode    =1   then Price end) PC1
  , max(case when ClrCode     = 2 then Price end) PC2
  , max(case when ClrCode     =3  then Price end) PC3
  , max(case when ClrCode     =4  then Price end) PC4
  , max(case when ClrCode     =5  then Price end) PC5
  , max(case when ClrCode     =6  then Price end) PC6
from yourtable
group by ArtCode
order by ArtCode         

Open in new window

0
 

Author Comment

by:24Carat
ID: 38773706
Hi AngelIII,

Could i put the values into a array and put a loop in there to enlarge the array with every dictinct value?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38773731
you can of course build the sql dynamically, either you have a table with the distinct ClrCode values, or you run such a query just upfront....
0
 

Author Comment

by:24Carat
ID: 38830090
I've been using other scripting languages around and in between to get it working. But i would need to create this all in MySQL query language for this project.

Can somebody assist me in translating this into the correct MySQL query syntax?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 38830102
would this help?
stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab
0
 

Author Comment

by:24Carat
ID: 38888444
Hi,

Thank yuo both for the suggestions and examples however at this moment it's to complicated for me to implement it.

Regads,
24Carat
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39114561
ArtCode,ClrCode_1,ClrCode_2,ClrCode_3,ClrCode_4,ClrCode_5,ClrCode_6

this snippet will produce a list of the columns seen above (and that will be in the next bit) so you can use them in whatever the calling application is (e.g. php)
set @cols = (
SELECT 
    GROUP_CONCAT(col_ref) AS col_refs
FROM (select distinct concat('ClrCode_' , `ClrCode`) as col_ref from table1) dc
);
set @cols = concat('ArtCode,', @cols);

select @cols
;

Open in new window

and this prepares and executes the dynamic 'pivot' query, using the columns names as given above
set @sql = (
SELECT 
    GROUP_CONCAT(col_ref)
FROM (
      select distinct
      concat(' max(case when ClrCode=',ClrCode,' then Price else NULL end) as ClrCode_',ClrCode) 
      as col_ref from table1
      ) dc
  );

set @sql = concat('select ArtCode,', @sql, ' from table1 Group By `ArtCode`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
;

Open in new window

e.g. the @sql wold look like this:
select ArtCode, max(case when ClrCode=1 then Price else NULL end) as ClrCode_1, max(case when ClrCode=2 then Price else NULL end) as ClrCode_2, max(case when ClrCode=3 then Price else NULL end) as ClrCode_3, max(case when ClrCode=4 then Price else NULL end) as ClrCode_4, max(case when ClrCode=5 then Price else NULL end) as ClrCode_5, max(case when ClrCode=6 then Price else NULL end) as ClrCode_6 from table1 Group By `ArtCode`

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39114564
see the above working at: http://sqlfiddle.com/#!9/896e9/2
0
 

Author Comment

by:24Carat
ID: 39114868
Awesome !!!

Just awesome !!!

Only in the example used above we have 6 ClrCode

What if we have 20+ ?

The max size of @sql variable seems to be limited.

Is this due to GROUP_CONCAT size limitations?
0
 

Author Comment

by:24Carat
ID: 39114871
I 'll put it in a new post.
0
 

Author Closing Comment

by:24Carat
ID: 39114886
When comparing it with angelIII's posts it is the same.

Only the article on stackoverflow was just too large and too much extra coding is shown to be able to easily visualize what is happening.

Thanks for the help.

regards
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39114943
Not quite the same :) but certainly inspired by angelII's post - glad you shared the points.

I wanted to understand that url actually, and think it could be generalized a bit further (given time) but also felt you wanted a "leg up" beyond just reading the approach.

I did 'add' the extra snippet which I think will allow your the app calling the sql to 'understand' what columns it is about to receive. my value add beyond mere replication.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39114951
Only in the example used above we have 6 ClrCode
What if we have 20+ ?
>> the code is dynamic, it will find the distinct values in that column
you would not want to do this on hundreds of distinct values of course!

The max size of @sql variable seems to be limited.
>> not sure

Is this due to GROUP_CONCAT size limitations?
>> not sure

not a MySQL specialist, but I'll look for relevant info.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39114953
oh, you can use the existing sqlfiddle for simple testing of more values etc.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39114982
GROUP_CONCAT has limitations:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.
0
 

Author Comment

by:24Carat
ID: 39115006
Hi AngelIII,

Thanks for the hint.

4096 will be enough i think.
SET @group_concat_max_len = 4096;

Just out of curiosity
max_allowed_packet = 1048576
does that mean that max group_concat_max_len = 1048576 ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39115012
mmm, then 1024/~67 ~= 15

each 'column' currently consumes:
max(case when ClrCode=1 then Price else NULL end) as ClrCode_nn,

this can be trimmed back marginally, e.g.

max(case when ClrCode=nn then Price end) ClrCode_nn,

could divide the query into more parts but gets uglier
maybe there's a way to avoid group_concat ...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39115047
I believe it means:
group_concat_max_len <= (max_allowed_packet = 1048576)

i.e. group_concat_max_len cannot exceed the number set by max_allowed_packet

my understanding is @sql and @cols are limited by max_allowed_packet
(but I could be wrong on this)
0
 

Author Comment

by:24Carat
ID: 39115122
seems easier to just increase the max size to 4096

4096/67 = 61

But i think i'll have to reboot the server because when counting the chars it is still limited to 1023




I believe the max distinct ClrCode in the table is around 30

It doesn't look like the table will be ever having more then +-60 distinct ClrCode values

the server isn't continuesly queuried with large GROUP_CONCATs
Or just large queries

so i don't think it will have much effect on stability/performance of the server
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39115152
a concept...  http://sqlfiddle.com/#!9/6a1c8/1
set @cols='ArtCode,';
set @sql = concat('select ', @cols);

select
  @sql     := concat(@sql,' max(case when ClrCode=',ClrCode,' then Price else NULL end) as ClrCode_',ClrCode,',') as qryvar
, @cols    := concat(@cols,'ClrCode_' , ClrCode,',') as colvar
from (
      select distinct
        ClrCode
      from table1
     ) as S
, (SELECT @sql  := @sql)  as qryvar
, (SELECT @cols := @cols) as colvar

;

set @cols = concat( @cols, 'x');
set @sql  = concat( @sql, '1 as x from table1 Group By `ArtCode`');

PREPARE stmt FROM @sql;
EXECUTE stmt;

;

Open in new window

0
 

Author Comment

by:24Carat
ID: 39115154
So luckily this thime i do have some cofee and figured out that

SET @group_concat_max_len = 4096;

is not working because it should be

SET @@group_concat_max_len = 4096;
0
 

Author Comment

by:24Carat
ID: 39115224
Very nice !!

Little harder to read but very nice !
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39115257
it's a concept

pity MySQL does not have "cte" then this approach would sing - oh well.

I may work on it - no promises.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39115337
http://sqlfiddle.com/#!9/6a1c8/5

set @tblFrom = 'Table1';
set @pivotOn = 'ArtCode';
set @colvar  = @pivotOn;
set @qryvar  = concat('SELECT ', @pivotOn);
set @aggfunc = 'MAX';

select
  @qryvar := concat(@qryvar,',',@aggfunc, '(case when ClrCode=',ClrCode,' then Price else NULL end) as ClrCode_',ClrCode) as qryvar
, @colvar := concat(@colvar,'ClrCode_' , ClrCode) as colvar
from (
      select distinct
        ClrCode
      from table1
     ) as S
, (SELECT @qryvar := @qryvar) as qryvar
, (SELECT @colvar := @colvar) as colvar

;

set @qryvar  = concat( @qryvar, ' from ', @tblFrom, ' Group By ', @pivotOn );

PREPARE stmt FROM @qryvar;
EXECUTE stmt;

;

Open in new window

a bit tidier, somewhat more generic
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now