[SQL] 동적 열 수로 MySQL의 피봇 행

동적 열 수로 MySQL의 피봇 행

나는 세 가지 다른 MySQL의 테이블이 있다고 가정하자 :

표 제품 :

id | name
 1   Product A
 2   Product B

표 파트너 :

id | name
 1   Partner A
 2   Partner B

표 판매 :

partners_id | products_id
          1             2
          2             5
          1             5
          1             3
          1             4
          1             5
          2             2
          2             4
          2             3
          1             1

나는 열과 행의 파트너 및 제품 테이블을 좀하고 싶습니다. 지금까지 나는 다음과 같은 출력을 얻을 수 있었다 :

name      | name      | COUNT( * )
Partner A   Product A          1
Partner A   Product B          1
Partner A   Product C          1
Partner A   Product D          1
Partner A   Product E          2
Partner B   Product B          1
Partner B   Product C          1
Partner B   Product D          1
Partner B   Product E          1

이 쿼리를 사용하여 :

SELECT partners.name, products.name, COUNT( * ) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id
LIMIT 0 , 30

하지만 내가 좋아하는 대신에 뭔가를하고 싶습니다 :

partner_name | Product A | Product B | Product C | Product D | Product E
Partner A              1           1           1           1           2
Partner B              0           1           1           1           1

문제는 열 번호는 제품 테이블의 행에 따라 동적으로 변경해야하므로 내가 가지고 얼마나 많은 제품에서 구별 할 수 없기 때문입니다.

T-SQL 피벗이 아주 좋은 대답은 MySQL과 연동하여 작동하지 않는 것? 행 값 테이블로부터 열을 만드는 가능성

해결법

1. ==============================

   1.불행하게도 MySQL은 기본적으로 당신이하려고하는 어떤 피벗 기능이 없습니다. 당신은 CASE 문으로 집계 함수를 사용해야합니다 그래서 :

   불행하게도 MySQL은 기본적으로 당신이하려고하는 어떤 피벗 기능이 없습니다. 당신은 CASE 문으로 집계 함수를 사용해야합니다 그래서 :

   select pt.partner_name,
     count(case when pd.product_name = 'Product A' THEN 1 END) ProductA,
     count(case when pd.product_name = 'Product B' THEN 1 END) ProductB,
     count(case when pd.product_name = 'Product C' THEN 1 END) ProductC,
     count(case when pd.product_name = 'Product D' THEN 1 END) ProductD,
     count(case when pd.product_name = 'Product E' THEN 1 END) ProductE
   from partners pt
   left join sales s
     on pt.part_id = s.partner_id
   left join products pd
     on s.product_id = pd.prod_id
   group by pt.partner_name
   

   데모와 SQL 바이올린을 참조하십시오

   당신이 제품을 알 수 없기 때문에 당신은 아마 동적으로이 작업을 수행 할 것입니다. 이것은 준비된 문을 사용하여 수행 할 수 있습니다.

   동적 피벗 테이블 (컬럼에 로우 변환)

   코드는 다음과 같을 것이다 :

   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 products;
   
   SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' from partners pt
   left join sales s
     on pt.part_id = s.partner_id
   left join products pd
     on s.product_id = pd.prod_id
   group by pt.partner_name');
   
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   

   데모와 SQL 바이올린을 참조하십시오

   그것은 GROUP_CONCAT 1024 바이트로 제한 기본적으로 있음을 지적 아마 가치가있다. 당신은 즉 프로 시저의 기간 동안 높은 설정하여이 문제를 해결할 수 있습니다. SET @@ group_concat_max_len = 32000;

from https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns by cc-by-sa and MIT license