Informix 12.10行转列实现求助：特定数据表结构转换需求

If you're looking to transform your original table into the pivoted format you shared, there are two straightforward approaches in Informix 12.10 depending on your exact version.

Option 1: Use the PIVOT Operator (For 12.10.xC6 and Later) #

Informix added native PIVOT support in version 12.10.xC6, which makes this kind of transformation clean and easy. Here's the query you can use:

Assuming your source table is named device_metrics:

SELECT 
    localcol,
    device,
    SA,
    PA,
    TA
FROM (
    -- First, unpivot the device columns into rows
    SELECT localcol, type, device_a AS val, 'Device A' AS device FROM device_metrics
    UNION ALL
    SELECT localcol, type, device_b AS val, 'Device B' AS device FROM device_metrics
    UNION ALL
    SELECT localcol, type, device_c AS val, 'Device C' AS device FROM device_metrics
) unpivoted_data
PIVOT (
    MAX(val)  -- Since each (localcol, device, type) has one value, MAX works perfectly
    FOR type IN ('SA' AS SA, 'PA' AS PA, 'TA' AS TA)
) AS pivoted_result
ORDER BY localcol, device;

How this works: #

1. The subquery takes each of your three device columns and converts them into individual rows, labeling each row with the corresponding device name.
2. The PIVOT clause then rotates the type values (SA/PA/TA) into columns, aggregating the values using MAX (which just grabs the single value present for each group).

Option 2: Conditional Aggregation (Compatible with All 12.10 Versions) #

If you're on an older 12.10 release that doesn't support PIVOT, conditional aggregation is a reliable alternative:

SELECT 
    localcol,
    device,
    -- Extract values for each type using CASE statements
    MAX(CASE WHEN type = 'SA' THEN val END) AS SA,
    MAX(CASE WHEN type = 'PA' THEN val END) AS PA,
    MAX(CASE WHEN type = 'TA' THEN val END) AS TA
FROM (
    -- Same unpivot step as before
    SELECT localcol, type, device_a AS val, 'Device A' AS device FROM device_metrics
    UNION ALL
    SELECT localcol, type, device_b AS val, 'Device B' AS device FROM device_metrics
    UNION ALL
    SELECT localcol, type, device_c AS val, 'Device C' AS device FROM device_metrics
) unpivoted_data
GROUP BY localcol, device
ORDER BY localcol, device;

Key Notes: #

• Replace device_metrics with your actual table name.
• If your original NA values are stored as string literals instead of Informix NULLs, adjust the subquery to convert them:

  CASE WHEN device_a = 'NA' THEN NULL ELSE device_a END AS val
  

  (Repeat this pattern for device_b and device_c.)
• Both queries return the exact format you requested, sorted by timestamp and device name.

内容的提问来源于stack exchange，提问作者Sajith PS