MySQL, multiple rows to separate fields

Question

I have a MySQL table with fields and data such as follows;

PartNumber  Priority SupName
a1            0        One
a2            0        One
a2            1        Two
a3            0        One
a4            1        Two
a5            2        Three

I am trying to create a view where the parts that have multiple rows are combined into a single row, and into separate fields such as

Ideally This;

PartNumber  Sup1  Sup2  Sup3
a1          One   NULL  NULL
a2          One   Two   NULL
a3          One   NULL  NULL
a4          Two   NULL  NULL
a5          Three NULL  NULL

Or I can live with this

PartNumber  Sup1  Sup2  Sup3
a1          One   NULL  NULL
a2          One   Two   NULL
a3          One   NULL  NULL
a4          NULL  Two   NULL
a5          NULL  NULL  Three

How would I build a view or select statement to accomplish this?

The closest I have come so far is;

SELECT PartNumber, 
       IF(Priority=0, SupName, NULL) AS Sup1, 
       IF(Priority=1, SupName, NULL) AS Sup2,
       IF(Priority=2, SupName, NULL) AS Sup3 
  FROM SupXref 
ORDER BY PartNumber

This however gives me a separate row for each of the fields and I need a single line.

that's a crosstab, and you'd be better off doing that kind of transformation in your client. Mysql does not support crosstab queries directly. — Marc B, Feb 20 '12 at 05:04
logic is little bit confusing. I think you can do with group_concat — , Feb 20 '12 at 05:07
Might want to add a tag for (or just append to the title) group-by, to make this more helpful to those who come searching :) — Will Buck, Feb 20 '12 at 05:10

Mosty Mostacho · Accepted Answer · 2012-02-20T05:52:52.797

You're just missing a group by :)

SELECT PartNumber,
  MAX(IF (Priority = 0, SupName, NULL)) AS Sup1,
  MAX(IF (Priority = 1, SupName, NULL)) AS Sup2,
  MAX(IF (Priority = 2, SupName, NULL)) AS Sup3
FROM SupXref
GROUP BY PartNumber

Edit:

After playing for a while I think I got the first solution you're looking for. Give it a try :)

SELECT partnumber,
  COALESCE(Sup1, COALESCE(Sup2, Sup3)) AS Supp1,
  IF (Sup1 IS NULL, IF (Sup2 IS NULL, NULL, Sup3), COALESCE(Sup2, Sup3)) AS Supp2,
  IF (Sup1 IS NULL, NULL, IF (Sup2 IS NULL, NULL, Sup3)) AS Supp3
FROM (
  SELECT PartNumber,
    MAX(IF (Priority = 0, SupName, NULL)) AS Sup1,
    MAX(IF (Priority = 1, SupName, NULL)) AS Sup2,
    MAX(IF (Priority = 2, SupName, NULL)) AS Sup3
  FROM SupXref
  GROUP BY PartNumber
) AS S

For the following table:

+------------+----------+---------+
| PARTNUMBER | PRIORITY | SUPNAME |
+------------+----------+---------+
| a1         |        2 | Three   |
| a2         |        1 | Two     |
| a3         |        2 | Three   |
| a3         |        1 | Two     |
| a4         |        0 | One     |
| a5         |        0 | One     |
| a5         |        2 | Three   |
| a6         |        0 | One     |
| a6         |        1 | Two     |
| a7         |        0 | One     |
| a7         |        1 | Two     |
| a7         |        2 | Three   |
+------------+----------+---------+

Data is turn into this:

+------------+------+------+-------+
| PARTNUMBER | SUP1 | SUP2 | SUP3  |
+------------+------+------+-------+
| a1         |      |      | Three |
| a2         |      | Two  |       |
| a3         |      | Two  | Three |
| a4         | One  |      |       |
| a5         | One  |      | Three |
| a6         | One  | Two  |       |
| a7         | One  | Two  | Three |
+------------+------+------+-------+

And finally into this:

+------------+-------+-------+-------+
| PARTNUMBER | SUPP1 | SUPP2 | SUPP3 |
+------------+-------+-------+-------+
| a1         | Three |       |       |
| a2         | Two   |       |       |
| a3         | Two   | Three |       |
| a4         | One   |       |       |
| a5         | One   | Three |       |
| a6         | One   | Two   |       |
| a7         | One   | Two   | Three |
+------------+-------+-------+-------+

Thanks, that does exactly what I wanted. I needed a SQL solution because the application (third-party and closed source) that needs to use this information does not allow me to add any code. I need to look closer at COALESCE. — T Schultz, Feb 20 '12 at 12:25

MySQL, multiple rows to separate fields

1 Answers1

Linked