# Collapse indicator columns into one

Shape / columns

A variant of [Encode categories as numbers](https://meridian.online/operations/encode-categories-as-numbers).

Turn a set of 0 or 1 indicator columns back into one column naming the indicator that is set.

Page: <https://meridian.online/operations/encode-categories-as-numbers/collapse-indicator-columns-into-one>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

collapse indicator columns into one column naming the one that is set

```sql
CREATE TEMP TABLE wide AS SELECT * FROM (VALUES
  (1, 1, 0, 0), (2, 0, 1, 0), (3, 0, 0, 1), (4, 0, 0, 0), (5, 1, 0, 0), (6, 1, 1, 0), (7, 1, 1, NULL)
) t(id, widget, gadget, gizmo);
-- none set gives NULL; two or more set (rows 6 and 7) gives 'multiple', so the collapse never picks one of them silently.
-- An unknown indicator (row 7's NULL gizmo) counts as not set: a plain sum would be NULL there, and the row would become 'widget'
CREATE TEMP TABLE result AS
SELECT id, CASE WHEN coalesce(widget, 0) + coalesce(gadget, 0) + coalesce(gizmo, 0) > 1 THEN 'multiple'
                WHEN widget = 1 THEN 'widget' WHEN gadget = 1 THEN 'gadget' WHEN gizmo = 1 THEN 'gizmo' END AS product FROM wide;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'product'] AND (SELECT list(product ORDER BY id) FROM result) = ['widget', 'gadget', 'gizmo', NULL, 'widget', 'multiple', 'multiple']) THEN 'as expected' ELSE error('the three indicator columns were not collapsed to one product column, with NULL where none is set and multiple where two are') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- KNIME Analytics Platform: [`Many to One`](https://api.hub.knime.com/nodes/*XbOgJJl-gFiDMtr1?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Many to One (PMML)`](https://api.hub.knime.com/nodes/*pu1W2Sq3367PuKdk?details=full) (read 2026-09-27)