[SQL] MySQL의 피벗 / 크로스 탭 쿼리

MySQL의 피벗 / 크로스 탭 쿼리

질문 1 : 나는 구조와 데이터 아래에있는 테이블을 가지고 :

app_id  transaction_id  mobile_no   node_id  customer_attribute  entered_value 
100     111             9999999999  1        Q1                  2                             
100     111             9999999999  2        Q2                  1                             
100     111             9999999999  3        Q3                  4                             
100     111             9999999999  4        Q4                  3                             
100     111             9999999999  5        Q5                  2                             
100     222             8888888888  4        Q4                  1                             
100     222             8888888888  3        Q3                  2                             
100     222             8888888888  2        Q2                  1                             
100     222             8888888888  1        Q1                  3                             
100     222             8888888888  5        Q5                  4                             

나는 아래의 형식으로이 레코드를 표시하려면 :

app_id  |  transaction_id  | mobile     |  Q1  |  Q2  |  Q3  |  Q4 |  Q5  |
 100    |      111         | 9999999999 |   2  |   1  |   4  |  3  |  2   |
 100    |      222         | 8888888888 |   3  |   1  |   2  |  1  |  4   |

나는이 디스플레이를 얻을 크로스 탭 / 피벗 쿼리를 사용할 필요가 알고있다. 이를 위해 나는 그것에 대해 가지고 제한된 지식을 바탕으로 그것을 시도. 다음은 내 쿼리입니다 :

SELECT app_id, transaction_id, mobile_no,
  (CASE node_id WHEN 1 THEN entered_value ELSE '' END) AS user_input1,
  (CASE node_id WHEN 2 THEN entered_value ELSE '' END) AS user_input2,
  (CASE node_id WHEN 3 THEN entered_value ELSE '' END) AS user_input3,
  (CASE node_id WHEN 4 THEN entered_value ELSE '' END) AS user_input4,
  (CASE node_id WHEN 5 THEN entered_value ELSE '' END) AS user_input5
FROM trn_user_log 
GROUP BY app_id, transaction_id, mobile_no, node_id

그리고이 쿼리를 기반으로 나는 화면 아래 가지고 :

app_id  transaction_id  mobile_no   user_input1  user_input2  user_input3  user_input4  user_input5  
100     111             9999999999  2                                                                
100     111             9999999999               1                                                   
100     111             9999999999                            4                                      
100     111             9999999999                                         3                         
100     111             9999999999                                                      2            
100     222             8888888888  3                                                                
100     222             8888888888               1                                                   
100     222             8888888888                            2                                      
100     222             8888888888                                         1                         
100     222             8888888888                                                      4            

나는 위와 같이 하나의 행의 레코드가 아니라 여러 행을 얻기 위해 내 쿼리를 만들 필요가 적절한 변화와 캔 사람의 도움 나.

질문 2 : 또한 컬럼의 이름으로 특정 필드의 값을 얻을 수있는 방법이있다. 내가 user_input1, user_input2이 위에 당신이 볼 수 있듯이, ... 헤더로. 대신에 나는 열 헤더로 customer_attribute의 값을 갖고 싶어 그.

이를 위해 나는 다음과 같이 NAME_CONST (이름, 값) 확인 :

SELECT app_id, transaction_id, mobile_no,
NAME_CONST(customer_attribute, (CASE node_id WHEN 1 THEN entered_value ELSE '' END))
FROM trn_user_log 

그러나 오류를 제공합니다

Error Code : 1210 Incorrect arguments to NAME_CONST

도움이 필요합니다.

해결법

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

   1.요한의 정적 대답은 잘 작동 @ 동안, 당신은 당신이, 내가 결과를 얻을 준비가 문을 사용하여 고려할 변환하려는 열의 알 수없는 번호가있는 경우 :

   요한의 정적 대답은 잘 작동 @ 동안, 당신은 당신이, 내가 결과를 얻을 준비가 문을 사용하여 고려할 변환하려는 열의 알 수없는 번호가있는 경우 :

   SET @sql = NULL;
   SELECT
     GROUP_CONCAT(DISTINCT
       CONCAT(
         'GROUP_CONCAT((CASE node_id when ',
         node_id,
         ' then entered_value else NULL END)) AS user_input',
         node_id
       )
     ) INTO @sql
   FROM trn_user_log;
   
   
   SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                     FROM trn_user_log 
                     GROUP BY app_id, transaction_id, mobile_no');
   
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   

   데모와 SQL 바이올린 참조

   지금까지 두 번째로, 당신이 분명하지 않다 무엇을하려고 명확히하시기 바랍니다.

2. ==============================

   2.당신의 CASE 절에서 GROUP_CONCAT 추가

   당신의 CASE 절에서 GROUP_CONCAT 추가

   SELECT app_id, transaction_id, mobile_no,
     GROUP_CONCAT((CASE node_id WHEN 1 THEN entered_value ELSE NULL END)) AS user_input1,
     GROUP_CONCAT((CASE node_id WHEN 2 THEN entered_value ELSE NULL END)) AS user_input2,
     GROUP_CONCAT((CASE node_id WHEN 3 THEN entered_value ELSE NULL END)) AS user_input3,
     GROUP_CONCAT((CASE node_id WHEN 4 THEN entered_value ELSE NULL END)) AS user_input4,
     GROUP_CONCAT((CASE node_id WHEN 5 THEN entered_value ELSE NULL END)) AS user_input5
   FROM trn_user_log 
   GROUP BY app_id, transaction_id, mobile_no
   

3. ==============================

   3.@DarkKnightFan이 내가 일하는 작업에 매우 도움이 질문이었다. 내가 나서서 두 번째 질문을 해결하기 위해 @bluefin에서 솔루션을 수정했습니다. 다음 코드는 크로스 탭의 결과 열 머리글로 customer_attribute의 값으로 원래 요청한 형식을 생성합니다.

   @DarkKnightFan이 내가 일하는 작업에 매우 도움이 질문이었다. 내가 나서서 두 번째 질문을 해결하기 위해 @bluefin에서 솔루션을 수정했습니다. 다음 코드는 크로스 탭의 결과 열 머리글로 customer_attribute의 값으로 원래 요청한 형식을 생성합니다.

   관련 변화는 변화했다 :

   ' then entered_value else NULL END)) AS user_input',
         node_id
   

   이에:

   ' then entered_value else NULL END)) AS ''',
             customer_attribute,''''
   

   전체 코드 :

   SET @sql = NULL;
   SELECT
     GROUP_CONCAT(DISTINCT
       CONCAT(
         'GROUP_CONCAT((CASE node_id when ',
         node_id,
         ' then entered_value else NULL END)) AS ''',
         customer_attribute,''''
       )
     ) INTO @sql
   FROM trn_user_log;
   
   
   SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                     FROM trn_user_log 
                     GROUP BY app_id, transaction_id, mobile_no');
   
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   

   다른 사용자가이 문제를 탐색하는 당신이 크로스 탭으로 시도되는 값의 많은 경우 GROUP_CONCAT ()는 1024 자의 기본 최대 길이를 가지고 있기 때문에 또한, 당신은 오류로 실행할 수 있습니다. 높이려면 준비된 문장의 시작이를 넣어 :

   SET SESSION group_concat_max_len = value; -- replace value with an int
   

from https://stackoverflow.com/questions/12382771/mysql-pivot-crosstab-query by cc-by-sa and MIT license