Solved

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

Posted on 2013-01-14
26
1,393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 4
  • +1
26 Comments
 
LVL 51

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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 143

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 143

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
 
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 143

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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