0

I am trying to write a pivot function in MySQL workbench and many of the places I've looked have not been super relevant.

I currently have:

order_ID     Part    Description    Order number
   1          103      A               1
   2          104      B               1
   3          103      A               2
   4          105      C               3
   5          103      A               4
   6          105      C               4
   7          107      D               4

I would like to create:

Order    Part1   Description     Part2  Description   Part3 Description  
 1         103        A           104     B
 2         103        A           
 3         105        C           
 4         103        A           105     C            107     D

I can keep the primary key in the output, but it is not necessary. The problem I am running into is that many pivot functions involve using distinct parts names to move them; however, I have over 500 parts. I also would like to move the description and the part together so they are next to each other--most pivot functions are not powerful enough to address that.

I did write a macro to do this in Excel, but it must be done in a database because of further analysis in R and I am pulling data from a database and I must automate any changes made to the data. As a result, I DO NOT have a choice in how the data is organized and laid out. Please do not mention normalizing data or other database techniques because I am trying to fix the data and how messy it is, but I DO NOT have a choice in how the data is inputted.

Some resources I used to gain experience with pivoting in MySQL, but I have not been able to get any code to work.

MySQL pivot table

mysql pivoting - how can I fetch data from the same table into different columns?

http://en.wikibooks.org/wiki/MySQL/Pivot_table

http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

Community
  • 1
  • 1
redracer67
  • 21
  • 1
  • 6

1 Answers1

0
Select group_concat(Table.column1) as anything, 
group_concat(Table.column2 separator ';') 
AS Anything2, Table.`column3`
FROM Table
group by Table.column3;

Alter TABLE Table ADD 
  `newcolumn1` varchar(100) DEFAULT '' after `column3`; 
Alter TABLE MB ADD 
  `newcolumn2` varchar(500) DEFAULT '' after `newcolumn1`;

UPDATE Table SET
    `newcolumn1` = IF ( 
        LOCATE(',', column1) >0,
        SUBSTRING(column1, 1,LOCATE(',', column1)-1),
        column1
),
    `newcolumn2` = IF( 
        LOCATE(',', column1) > 0,
        SUBSTRING(column1, LOCATE(',', column1)+1),
        ''); 

UPDATE Table SET 
    newcolumn2 = SUBSTRING_INDEX(newcolumn2, ',', 1);
UPDATE Table SET 
    newcolumn3 = SUBSTRING_INDEX(newcolumn3, ',', 1);

This code achieved exactly the format I wanted above.

redracer67
  • 21
  • 1
  • 6