동적 열이 있는 MySQL 피벗 테이블 쿼리

동적 열이 있는 MySQL 피벗 테이블 쿼리

제품 데이터를 저장하기 위해 다음 표를 사용하고 있습니다.

mysql> SELECT * FROM product;
+---------------+---------------+--------+
| id | name     | description   | stock  |
+---------------+---------------+--------+
|  1 | product1 | first product |    5   | 
|  2 | product2 | second product|    5   | 
+---------------+---------------+--------+

mysql> SELECT * FROM product_additional;
+-----------------+------------+
| id | fieldname  | fieldvalue |
+-----------------+------------+
|  1 | size       | S          |
|  1 | height     | 103        |
|  2 | size       | L          |
|  2 | height     | 13         |
|  2 | color      | black      |
+-----------------+------------+

다음 쿼리를 사용하여 두 테이블에서 레코드 선택

mysql> SELECT 
    p.id
    , p.name
    , p.description
    ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`
    ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`
    ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`
FROM product p
LEFT JOIN product_additional AS pa ON p.id = pa.id
GROUP BY p.id
+---------------+---------------+--------+---------+--------+
| id | name     | description   | size   | height  | color  |
+---------------+---------------+--------+---------+--------+
|  1 | product1 | first product | S      | 103     | null   |
|  2 | product2 | second product| L      | 13      | black  |
+---------------+---------------+--------+---------+--------+

그리고 모든 것이 올바르게 작동하고 있습니다 :)

제가 '추가' 테이블을 동적으로 채우기 때문에 쿼리도 동적이면 좋을 것입니다.그런 식으로 새 필드 이름과 필드 값을 입력할 때마다 쿼리를 변경할 필요가 없습니다.

MySQL에서 동적으로 이 작업을 수행할 수 있는 유일한 방법은 준비된 문입니다.다음은 그들에 대한 좋은 기사입니다.

동적 피벗 테이블(행을 열로 변환)

코드는 다음과 같습니다.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

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

데모 보기

참고: GROUP_CONCAT 함수의 최대 문자 수는 1024자입니다.매개 변수 group_concat_max_len 참조

저는 이것을 하는 방법이 공인된 답변과 조금 다릅니다.이렇게 하면 GROUP_CONCAT을 사용하지 않아도 됩니다. GROUP_CONCAT은 기본적으로 1024자로 제한되며, 제한을 변경하지 않으면 필드가 많으면 작동하지 않습니다.

SET @sql = '';
SELECT
    @sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
    SELECT
      DISTINCT
        CONCAT(
         'MAX(IF(pa.fieldname = ''',
          fieldname,
          ''', pa.fieldvalue, NULL)) AS ',
          fieldname
        ) as output
    FROM
        product_additional
) as temp;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

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

데모를 보려면 여기를 클릭하십시오.

저장 프로시저는 한 테이블 및 열의 데이터와 다른 테이블 및 열의 데이터를 기반으로 테이블을 생성합니다.

값 '으로서 'sum(if(col = value, 1,0))' 함수가 사용됩니다.MAX(if() 등과 같은 다양한 기능 중에서 선택할 수 있습니다.

delimiter //

  create procedure myPivot(
    in tableA varchar(255),
    in columnA varchar(255),
    in tableB varchar(255),
    in columnB varchar(255)
)
begin
  set @sql = NULL;
    set @sql = CONCAT('select group_concat(distinct concat(
            \'SUM(IF(', 
        columnA, 
        ' = \'\'\',',
        columnA,
        ',\'\'\', 1, 0)) AS \'\'\',',
        columnA, 
            ',\'\'\'\') separator \', \') from ',
        tableA, ' into @sql');
    -- select @sql;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
    -- select @sql;

    SET @sql = CONCAT('SELECT p.', 
        columnB, 
        ', ', 
        @sql, 
        ' FROM ', tableB, ' p GROUP BY p.',
        columnB,'');

    -- select @sql;

    /* */
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    /* */
    DEALLOCATE PREPARE stmt;
end//

delimiter ;

언급URL : https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns