如何使用HiveQL实现表格转置操作？

To achieve the table transposition you're looking for, we'll use a two-step approach: unpivoting the source table to convert roll columns into rows, then pivoting the result to turn subjects into columns using conditional aggregation (since Hive doesn't have a native PIVOT function).

Step 1: Unpivot the Source Table #

First, we'll convert the wide source table into a long format where each roll number and its score become individual rows. We can use LATERAL VIEW EXPLODE with a map to do this efficiently:

SELECT 
  subject, 
  roll_name, 
  score
FROM source_table
LATERAL VIEW EXPLODE(
  map(
    'roll_1', roll_1,
    'roll_2', roll_2,
    'roll_3', roll_3,
    'roll_4', roll_4,
    'roll_5', roll_5
  )
) t AS roll_name, score;

This query will output rows like:

Step 2: Pivot to Get the Target Format #

Next, we'll group by the roll names and use conditional aggregation to create columns for each subject, pulling in their respective scores:

SELECT 
  roll_name AS subject,
  MAX(CASE WHEN subject = 'MATHS' THEN score END) AS MATHS,
  MAX(CASE WHEN subject = 'ENGLISH' THEN score END) AS ENGLISH
FROM (
  -- Unpivot subquery from Step 1
  SELECT 
    subject, 
    roll_name, 
    score
  FROM source_table
  LATERAL VIEW EXPLODE(
    map(
      'roll_1', roll_1,
      'roll_2', roll_2,
      'roll_3', roll_3,
      'roll_4', roll_4,
      'roll_5', roll_5
    )
  ) t AS roll_name, score
) unpivoted_data
GROUP BY roll_name
ORDER BY roll_name;

Explanation: #

• The subquery converts the wide source table into a long format.
• GROUP BY roll_name groups all scores by each roll number.
• MAX(CASE ...) acts as a pivot: for each roll, it picks the score corresponding to the subject (since each roll-subject pair has exactly one score, MAX safely returns that value).
• ORDER BY roll_name ensures the rows are sorted in the order of roll_1 to roll_5.

This query will produce exactly the target table format you requested.

Content of the question is from Stack Exchange, asked by Amit Chandavale