MySQL SELECT - Switch Row With Column

Question

I apologize for the lack of explanation on the title, as I do not know what to really call what I am trying to do, please modify if you know what I am looking for.

Here is my current table:

prodID | seller  |  price
123      seller1    5000
123      seller2    3000
123      seller3    5500
123      seller4    3500
500      seller2    55
500      seller5    60
200      seller1    35
200      seller2    25
200      seller3    35

Now I want to GROUP BY prodID above and list each column as a the sellers price which could potentially be NULL, ie:

prodID  | seller1 | seller2 | seller3 | seller4 | seller5
123       5000      3000      5500      3500      null
500       null      55        null      null      60
200       35        25        35        null      null

I do not know how many unique sellers there are, so think of them as dynamic and not fixed.

I dont think I need a PIVOT TABLE from what I have been researching, but please correct me if wrong. I have also tried a UNION and combining the same table, but this seems inefficient as I dont know how many sellers there are and what their names are:

Inefficient:

SELECT 
    MT.prodID, MT.price, CT.price
from
    `table` MT,
    `table` CT
WHERE
    MT.prodID = CT.prodID
        AND MT.seller != CT.seller
GROUP BY MT.prodID

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — cs_alumnus, Mar 21 '14 at 15:43

score 0 · Accepted Answer · answered Mar 21 '14 at 16:08

well if you aren't trying to do a PIVOT TABLE and can't do this in another programming language (which would be the best options for this)... something you can do is this.

SELECT 
    t.prodID, 
    t1.seller1, 
    t.seller2, 
    t2.seller3, 
    t3.seller4, 
    t4.seller5 
FROM 
(
    SELECT
        m1.prodID, m1.price AS seller2
    FROM myTable AS m1
    WHERE m1.seller LIKE "seller2"
) AS t

LEFT JOIN
(
    SELECT
        m.prodID,
        m.price AS seller1
    FROM myTable AS m
    WHERE m.seller LIKE "seller1"
) AS t1 ON t.prodID = t1.prodID

LEFT JOIN
(
    SELECT
        m2.prodID, 
        m2.price AS seller3
    FROM myTable AS m2
    WHERE m2.seller LIKE "seller3"
) AS t2 ON t2.prodID = t.prodID

LEFT JOIN
(
    SELECT
        m3.prodID, 
        m3.price AS seller4
    FROM myTable AS m3
    WHERE m3.seller LIKE "seller4"
) AS t3 ON t3.prodID = t.prodID

LEFT JOIN
(
    SELECT
        m4.prodID, 
        m4.price AS seller5
    FROM myTable AS m4
    WHERE m4.seller LIKE "seller5"
) AS t4 ON t4.prodID = t.prodID

this is kinda a hacky way to do it, but it transposes your data per your request. see the sql fiddle for results... http://sqlfiddle.com/#!2/55f59/45

I like this idea, though its not the best way because of my dynamic sellers (currently 108). I am willing to do a `PIVOT TABLE`, I just didn't know one can be done with Procedures because of my dynamic sellers. — ToddN, Mar 21 '14 at 16:18
really you should do this in another programming language... I don't know which one your using.. but you can easily transpose all of the data with a few lines of code in a different programming language — John Ruddell, Mar 21 '14 at 20:42

MySQL SELECT - Switch Row With Column

1 Answers1