UNPIVOT 함수가 없을 때 MySQL에서 동적 열을 사용하여 데이터 피벗을 해제하는 방법은 무엇입니까?

동적 열을 사용하여 데이터 피벗 해제

쿼리 변환 영역에서 데이터 피벗 해제는 테이블 구조를 재구성하는 데 중요한 역할을 합니다. 그러한 시나리오 중 하나는 동적 열을 행으로 바꾸는 것과 관련이 있는데, 이는 MySQL의 UNPIVOT 함수 부족으로 인해 방해받을 수 있는 작업입니다.

문제: 동적 열 전치

Table_1이라는 테이블을 상상해 보세요. 표시된 것처럼 다양한 속성을 나타내는 동적 열(abc, brt, ccc 등)이 있습니다. 아래:

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

Table_1을 0이 아닌 값만 사용하여 열을 행으로 나열하는 Expected_Result_Table로 변환하는 것이 목표입니다.

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

해결책: 동적 UNION ALL 및 준비된 진술

이러한 변화를 달성하기 위해 UNION의 조합을 활용할 수 있습니다. 동적 열을 처리하기 위한 ALL 및 준비된 문:

1. 동적 SQL 생성: information_schema.columns를 사용하면 0이 아닌 각 열에 대해 행을 선택하는 동적 SQL을 생성할 수 있습니다.
2. Prepare 문: 준비된 문을 사용하여 동적 SQL을 실행하고 잠재적인 SQL 주입을 방지합니다.
3. 문 실행: 준비된 문을 실행하여 피벗되지 않은 데이터를 검색합니다.

동적 코드는 다음과 같습니다. 솔루션:

-- Generate dynamic SQL
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

결과:

결과 테이블에는 원하는 행이 포함됩니다.

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

위 내용은 UNPIVOT 함수가 없을 때 MySQL에서 동적 열을 사용하여 데이터 피벗을 해제하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!