CODEWITHSUNDEEP
mysqljoinmatrix
Kafin
Kafin

Reputation: 35

mysql query result as a matrix table based on the 2 fields on different tables

There are 2 Database table like below there Table_1 & Table_2. Is it possible to fetch the query result as a table matrix based on the 2 fields on different tables?

Table_1

| ID_PRODUCT    | NAME_PRODUCT  |
|------------   |-------------- |
| 1             | PRODUCT A     |
| 2             | PRODUCT B     |
| 3             | PRODUCT C     |

Table_2

| ID_SURVEY     | ID_PRODUCT    | TOTAL SALES   |
|-----------    |------------   |-------------  |
| 1             | 1             | 10            |
| 1             | 2             | 20            |
| 2             | 2             | 30            |
| 2             | 3             | 40            |
| 3             | 3             | 50            |

Expected Output:

| ID_SURVEY     | PRODUCT A     | PRODUCT B     | PRODUCT C     |
|-----------    |-----------    |-----------    |-----------    |
| 1             | 10            | 20            | 0             |
| 2             | 0             | 30            | 40            |
| 3             | 0             | 0             | 50            |

Upvotes: 1

Views: 393

Answers (1)

Blank
Blank

Reputation: 12378

If table_1 has fixed product, you can do it like this,

select
    ID_SURVEY,
    max(case when ID_PRODUCT = 1 then `TOTAL SALES` else 0 end) as `PRODUCT A`,
    max(case when ID_PRODUCT = 2 then `TOTAL SALES` else 0 end) as `PRODUCT B`,
    max(case when ID_PRODUCT = 3 then `TOTAL SALES` else 0 end) as `PRODUCT C`
from Table_2
group by ID_SURVEY

Demo Here

But here you have to use dynamic sql to do this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN t2.ID_PRODUCT = ',
      t2.ID_PRODUCT,
      ' THEN t2.`TOTAL SALES` ELSE 0 END) AS `',
      t1.NAME_PRODUCT, '`'
    )
  ) INTO @sql
FROM Table_2 t2
JOIN Table_1 t1
ON t2.ID_PRODUCT = t1.ID_PRODUCT;
SET @sql = CONCAT('SELECT t2.ID_SURVEY, ', @sql,
                  ' FROM Table_2 t2
                    JOIN Table_1 t1
                    ON t2.ID_PRODUCT = t1.ID_PRODUCT
                    GROUP BY t2.ID_SURVEY');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo Here

Upvotes: 1

Related Questions