mysql pivot using column and row numbers

Question

I am stuck in this situation where I need to use Row Number and Column Number values from table's columns to derive the output mentioned below. I have tried everything - if/else, case when/then but not helping.

Any help/suggestions are really appreciated!

Here is a mocked up sample data present in db table -

+--------+--------+--------+----------+-------------+ 
| Record | ColNbr | RowNbr | ColTitle | CellContent | 
+--------+--------+--------+----------+-------------+
|      1 | 1      | 1      | Unit     | sqf         |
|      1 | 1      | 2      | Unit     | cm          |
|      1 | 2      | 1      | Desc     | roof        |
|      1 | 2      | 2      | Desc     | rod         |
|      1 | 3      | 1      | Material | concrete    |
|      1 | 3      | 2      | Material | steel       |
|      1 | 4      | 1      | Quantity | 100         |
|      1 | 4      | 2      | Quantity | 12          |
|      1 | 1      | 1      | Unit     | liter       |
|      1 | 1      | 2      | Unit     | ml          |
|      1 | 2      | 1      | Desc     | bowl        |
|      1 | 2      | 2      | Desc     | plate       |
|      1 | 3      | 1      | Material | plastic     |
|      1 | 3      | 2      | Material | glass       |
|      1 | 4      | 1      | Quantity | 2           |
|      1 | 4      | 2      | Quantity | 250         |
+--------+--------+--------+----------+-------------+

Expected Output -

+--------+--------+--------+----------+-------------+ 
| Record | Unit   | Desc   | Material | Quantity    | 
+--------+--------+--------+----------+-------------+
|      1 | sqf    | roof   | concrete | 100         |
|      1 | cm     | rod    | steel    | 12          |
|      2 | liter  | bowl   | plastic  | 2           |
|      2 | ml     | plate  | glass    | 250         |
+--------+--------+--------+----------+-------------+

score 0 · Answer 1 · answered Jul 25 '19 at 03:15

If your actual data is like that, I suggest that you consider to separate the data to; for example, 4 different tables (unit,description,material & a table to store all that ids+quantity). The former 3 tables will store the prerequisite info that get minor updates throughout time and the last table will store all the quantity records. Let's say your tables will look something like this:

CREATE TABLE `Unit` (
unit_id INT,
unit_name VARCHAR(50));

+---------+-----------+
| unit_id | unit_name |
+---------+-----------+
|    1    | sqf       |
|    2    | cm        |
|    3    | liter     |
|    4    | ml        |
+---------+-----------+

CREATE TABLE `Description` (
desc_id INT,
desc_name VARCHAR(50));

+---------+-----------+
| desc_id | desc_name |
+---------+-----------+
|    1    | roof      |
|    2    | rod       |
|    3    | bowl      |
|    4    | plate     |
+---------+-----------+

CREATE TABLE `Material` (
mat_id INT,
mat_name VARCHAR(50));

+--------+----------+
| mat_id | mat_name |
+--------+----------+
|    1   | concrete |
|    2   | steel    |
|    3   | plastic  |
|    4   | glass    |
+--------+----------+

CREATE TABLE `Records` (
unit_id INT,
desc_id INT,
mat_id INT,
quantity DECIMAL(14,4));

+---------+---------+--------+----------+ 
| unit_id | desc_id | mat_id | Quantity | 
+---------+---------+--------+----------+
|    1    |    1    |    1   | 100      |
|    2    |    2    |    2   | 12       |
|    3    |    3    |    3   | 2        |
|    4    |    4    |    4   | 250      |
+---------+---------+--------+----------+

Then you insert the data accordingly.

Anyhow, for your existing data example, it could be done but there are some concern over whether the unit+desc+material+quantity matching are correct. The only way I can maybe at least think that it's correctly matched is by giving all of the query a similar ORDER BY clause. Hence, the following:

SELECT A.record,A.unit,B.Desc,C.Material,D.Quantity FROM
     (SELECT @rn:=@rn+1 AS record,CASE WHEN coltitle='unit' THEN cellcontent END AS Unit 
        FROM yourtable, (SELECT @rn :=0 ) v 
      HAVING unit IS NOT NULL
      ORDER BY colnbr) A LEFT JOIN
     (SELECT @rn1:=@rn1+1 AS record,CASE WHEN coltitle='Desc' THEN cellcontent END AS `Desc`
        FROM yourtable, (SELECT @rn1 :=0 ) v 
      HAVING `Desc` IS NOT NULL
      ORDER BY colnbr) B ON a.record=b.record LEFT JOIN 
     (SELECT @rn2:=@rn2+1 AS record,CASE WHEN coltitle='material' THEN cellcontent END AS Material 
        FROM yourtable, (SELECT @rn2:=0 ) v 
      HAVING Material IS NOT NULL
      ORDER BY colnbr) C ON a.record=c.record LEFT JOIN 
     (SELECT @rn3:=@rn3+1 AS record,CASE WHEN coltitle='Quantity' THEN cellcontent END AS Quantity 
        FROM yourtable, (SELECT @rn3:=0 ) v 
      HAVING Quantity IS NOT NULL
      ORDER BY colnbr) D ON a.record=d.record;

The idea here is to make a sub-query based on COLTITLE then assign a numbering/ranking (@rn,@rn1,@rn2,@rn3) variable to each of the sub-query and join them up using LEFT JOIN. Now, this experiment works to exactly return the output that you need but its not a definite answer because there are some part that is questionable especially on matching the combination correctly. Hopefully, this will give you some idea.

mysql pivot using column and row numbers

1 Answers1