여러 열을 기반으로 MySQL 행을 열로 변환하는 방법은 무엇입니까?

여러 열을 기반으로 행을 열로 변환하는 MySQL 쿼리

소개:

동적으로 행을 열로 변환하는 것은 데이터 조작에서 일반적인 작업입니다. 이 기사에서는 MySQL 쿼리를 사용하여 단일 열 변환을 처리했던 이전 솔루션의 기능을 확장하는 솔루션을 살펴봅니다.

문제:

데이터와 해당 가격을 나타내는 여러 행이 있고 주문 및 항목별로 구성된 테이블입니다. 목표는 이 데이터를 각 주문이 열로 표시되고 각 항목이 행으로 표시되어 해당 가격이 표시되는 테이블로 변환하는 것입니다.

해결책:

이러한 다중 열 변환을 달성하기 위해 우리는 2단계 프로세스를 사용합니다.

1단계: 피벗 해제 데이터

UNION ALL을 사용하여 데이터 피벗을 해제하여 각 주문 및 항목 조합에 대해 여러 행을 생성하는 것으로 시작합니다. 이를 통해 다음 단계에서 데이터를 더 쉽게 조작할 수 있습니다.

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

2단계: 피벗되지 않은 데이터 피벗

데이터가 피벗 해제되면 피벗합니다. CASE 문과 함께 집계 함수를 사용하여 열로 다시 반환합니다. 피벗되지 않은 행을 ID별로 그룹화하고 해당 값을 적절한 열에 할당합니다.

SELECT
  id, 
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

동적 쿼리 생성:

다양한 주문 수의 테이블을 처리하려면 및 항목을 사용하여 준비된 쿼리를 동적으로 생성할 수 있습니다. 명령문.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `', 
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

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

결과:

결과 테이블은 각 주문을 열로, 각 항목을 행으로 사용하여 데이터를 간결하게 표현합니다. 해당 가격입니다.

위 내용은 여러 열을 기반으로 MySQL 행을 열로 변환하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!