Wide<->long is lossless only if the grain survives the trip — unpivot, transform, re-pivot is the honest way to compute across columns.

Wide form is a reading layout; long form is a computing layout. The moment a question spans columns — 'which metric deviates most', 'normalize each metric by its fleet average' — wide form forces one expression per column and dies at N columns, while long form makes metric a value you can GROUP BY. Losslessness has two enemies: dropping the label and collapsing duplicates en route.

SELECT
      canoe,
  CASE
    WHEN hull_hours >= patch_count THEN ' hull_hours '
    ELSE 'patch_count'
  END AS metricFROM
  fleet
ORDER BY
      canoe;

WITH
  long AS (
    SELECT
      canoe,
      'hull_hours' AS metric,
      hull_hours AS v
    FROM
      fleet
    UNION ALL
    SELECT
      canoe,
      'patch_count',
      patch_count
    FROM
      fleet
  ),
  dev AS (
    SELECT
      canoe,
      metric,
      v - AVG(v) OVER (
        PARTITION BY
          metric
      ) AS above
    FROM
      long
  )
SELECT
  canoe,
  metric
FROM
  dev
QUALIFY
  ROW_NUMBER() OVER (
    PARTITION BY
      canoe
    ORDER BY
      above DESC
  ) = 1
ORDER BY
  canoe;

The cross-metric answer came from wide form — comparing raw hull_hours against patch_count picked hull for c2, where the long-form route (deviation from each metric's fleet average) correctly picks patch_count.

Lateral VALUES unpivot → compute in long form → conditional-agg re-pivot for the final human-facing shape.