如何在Vertica列式数据库中转置表（禁用CASE语句）

Got it, let's tackle this column-to-row transpose problem in Vertica without using CASE statements. Here are a couple of solid approaches, including a dynamic one for when you don't want to hardcode column names:

Static Approach (Known Columns) #

Using UNION ALL #

Instead of CASE statements, we can split each column into its own SELECT query and combine them with UNION ALL. This turns every column from your original table into rows of column names and their corresponding values:

SELECT emp_no, 'salary' AS column_name, salary::VARCHAR AS column_value
FROM your_table
UNION ALL
SELECT emp_no, 'from_date' AS column_name, from_date::VARCHAR AS column_value
FROM your_table
UNION ALL
SELECT emp_no, 'to_date' AS column_name, to_date::VARCHAR AS column_value
FROM your_table
ORDER BY emp_no, column_name;

Note: We cast all values to VARCHAR because UNION ALL requires consistent data types across all queries, and your table has mixed types (numeric salary, date columns).

Using Vertica's UNPIVOT (Cleaner Built-In Option) #

Vertica has a native UNPIVOT operator designed exactly for this transpose task, and it doesn't rely on CASE statements at all:

SELECT emp_no, column_name, column_value
FROM your_table
UNPIVOT (
  column_value FOR column_name IN (salary, from_date, to_date)
) AS unpivoted_results
ORDER BY emp_no, column_name;

This will output each original column as a row, paired with its value and the associated emp_no—exactly the transposed structure you need.

Dynamic Approach (For Variable Columns) #

If your table might have columns added or removed later, hardcoding column names isn't practical. We can use Vertica's system catalog tables to generate the transpose query automatically:

Dynamic UNION ALL Query #

DO $$
DECLARE
  transpose_query VARCHAR;
BEGIN
  -- Build the UNION ALL query dynamically using system metadata
  SELECT STRING_AGG(
    'SELECT emp_no, ''' || column_name || ''' AS column_name, ' || column_name || '::VARCHAR AS column_value FROM your_table',
    ' UNION ALL '
  ) INTO transpose_query
  FROM columns
  WHERE table_name = 'your_table'  -- Replace with your actual table name
    AND column_name != 'emp_no';

  -- Add ordering and execute the final query
  EXECUTE transpose_query || ' ORDER BY emp_no, column_name';
END;
$$;

Dynamic UNPIVOT Query (More Concise) #

If you prefer the cleaner syntax of UNPIVOT but still need dynamic column handling:

DO $$
DECLARE
  transpose_query VARCHAR;
  column_list VARCHAR;
BEGIN
  -- Get a comma-separated list of non-emp_no columns from system metadata
  SELECT STRING_AGG(column_name, ', ') INTO column_list
  FROM columns
  WHERE table_name = 'your_table'
    AND column_name != 'emp_no';

  -- Build the dynamic UNPIVOT query
  transpose_query := 'SELECT emp_no, column_name, column_value FROM your_table UNPIVOT (column_value FOR column_name IN (' || column_list || ')) AS unpivoted ORDER BY emp_no, column_name';

  -- Execute the generated query
  EXECUTE transpose_query;
END;
$$;

How This Dynamic Logic Works #

• Vertica's columns system table stores metadata for all tables and columns in your database. We filter it to get only the columns from your target table (excluding emp_no, which we keep as a grouping key).
• STRING_AGG concatenates column names or individual SELECT statements into a single, valid query string.
• The EXECUTE command runs the dynamically built query, so you don't have to manually update the code if your table schema changes.

That should solve your problem of iterating over multiple columns to convert them into rows—no CASE statements required!

内容的提问来源于stack exchange，提问作者Rajdeep Sengupta