Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-14
26
Medium Priority
?
1,452 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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1000 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 49

Accepted Solution

by:
PortletPaul earned 1000 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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

926 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