MySQL에서 열을 행으로 동적으로 피벗 해제하는 방법은 무엇입니까?

동적 열을 행으로 전치

종종 "피벗 해제"라고도 하는 열을 행으로 전치하는 것은 데이터 재구성에 유용한 기술이 될 수 있습니다. . 여러 열이 있는 넓은 테이블을 더 적은 수의 열이 있는 좁은 테이블로 변환하면 데이터 분석 및 작업이 더 쉬워집니다.

MySQL의 동적 언피버팅

MySQL에서는 내장된 UNPIVOT 기능이 없습니다. 그러나 SQL 문과 동적 SQL 생성의 조합을 사용하면 피벗 해제를 달성할 수 있습니다.

예: Table_1 피벗 해제

Table_1의 피벗 해제를 원하는 다음 예를 고려해 보세요. 다음보다 큰 값만 고려하여 Expected_Result_Table에 추가합니다. 0:

Table1
-----------------------------------------
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

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

Unpivoting을 위한 동적 SQL 생성

Table_1을 unpivot하려면 먼저 Table_1의 열 이름을 기반으로 동적 SQL 문을 생성해야 합니다. 테이블_1. 이는 다음 단계를 사용하여 수행할 수 있습니다.

1. 다음 쿼리를 사용하여 "Id" 열을 제외한 고유한 열 이름 목록을 생성합니다.

   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;

2. 생성된 SQL 문자열을 연결하여 최종 동적 SQL을 구성합니다. 명령문:

   SET @sql = CONCAT('select id, word, qty
               from
               (', @sql, ') x  order by id');

3. 다음 코드를 사용하여 동적 SQL 문을 준비하고 실행합니다.

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

결과:

동적 SQL 문을 실행하면 예상_결과_테이블:

---------------------
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

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