using pivot function in mysql to make a table apriori

Question

this was almost same cases in this case MySQL pivot row into dynamic number of columns but the results was different and it make me confuse.

let's say i have 3 tables

create table order_match
(
id int(10) PRIMARY KEY not null,
order_status_id int(10) not null
);

create table order_match_detail
(
 id int(10) PRIMARY KEY not null,
 order_match_id int(10) not null,
 product_id int(10) NOT NULL
);

create table product
(
id int(10) PRIMARY KEY not null,
name varchar(255) not null
);

Insert into order_match (id, order_status_id)
select 1, 6 union all
select 2, 7 union all
select 3, 6 union all
select 4, 6;

Insert into order_match_detail (id, order_match_id, product_id)
select 1, 1, 147  union all
select 2, 2, 148 union all
select 3, 3, 147 union all
select 4, 4, 149 union all
select 5, 4, 147;

Insert into product (id, name)
select 147, 'orange' union all
select 148, 'carrot' union all
select 149, 'Apple';

with order_match.id = order_match_detail.order_match_id and order_match_detail.product_id = product.id

so like the previous case in MySQL pivot row into dynamic number of columns i want to input the product name with the transaction in order_status_id not in 7 (because 7 is expired transaction and denied)

the expected results was like this :
id (in order_match)    |    Orange  |  Carrot  |   Apple

1                           1            0           0
3                           1            0           0
4                           1            0           1

based on solution in previous cases, i used this

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when product.name = ''',
      product.name,
      ''' then 1 end) AS ',
      replace(product.name, ' ', '')
    )
  ) INTO @sql
from product;

SET @sql = CONCAT('SELECT omd.order_match_id, ', @sql, ' from order_match_detail omd
left join order_match om
  on omd.order_match_id = om.id
left join product p
  on omd.product_id = p.id
where om.order_status_id in (4, 5, 6, 8)
group by omd.order_match_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

but idk why return 0 and it's no way

this is the fiddle https://www.db-fiddle.com/f/nDe3oQ3VdtfS5QDokieHN4/6

As already mentioned on more than one occasion: https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query. That said , your best option (and again as already mentioned) is to handle issues of data display in application code — Strawberry, Jun 30 '20 at 07:22
Add `SELECT @sql;` into your fiddle. Execute. Then copy its `FROM` section, add `SELECT *` to it and execute. Investigate the result. — Akina, Jun 30 '20 at 08:04
*idk why return 0* Because there is no a row in `sales` with partner_id=2 and product_id=1. — Akina, Jun 30 '20 at 08:08

score 0 · Answer 1 · answered Aug 29 '20 at 08:45

For your GROUP_CONCAT query; in your case stmt, you are referring to your product table as product itself. But in your join query, you are referring to product table as alias p. Since the first group_concat query is a part of the join query, you need to keep the table aliases same.(made changes at line 5)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when p.name = ''',  
      product.name,
      ''' then 1 end) AS ',
      replace(product.name, ' ', '')
    )
  ) INTO @pivotsql
from product;

SET @sql = CONCAT('SELECT omd.order_match_id, ', @pivotsql, ' from order_match_detail omd
left join order_match om
  on omd.order_match_id = om.id
left join product p
  on omd.product_id = p.id
  where om.order_status_id in (4, 5, 6, 8)
group by omd.order_match_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

using pivot function in mysql to make a table apriori

1 Answers1

Linked