• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1482
  • Last Modified:

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

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
24Carat
Asked:
24Carat
  • 11
  • 10
  • 4
  • +1
2 Solutions
 
Rgonzo1971Commented:
0
 
24CaratAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
24CaratAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
24CaratAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would this help?
stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab
0
 
24CaratAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
see the above working at: http://sqlfiddle.com/#!9/896e9/2
0
 
24CaratAuthor Commented:
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
 
24CaratAuthor Commented:
I 'll put it in a new post.
0
 
24CaratAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
oh, you can use the existing sqlfiddle for simple testing of more values etc.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
24CaratAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
24CaratAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
24CaratAuthor Commented:
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
 
24CaratAuthor Commented:
Very nice !!

Little harder to read but very nice !
0
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now