Turning Repeating Columns Into a Proper Child Table

A sheet with Phone 1, Phone 2 and Phone 3 columns is easy to read and awkward to use. Searching for a number means checking three columns. Adding a fourth phone means adding a column to the sheet and to every formula that touches the others. Most cells in Phone 3 are empty. The same shape appears with Skill 1, Skill 2, Tag 1 to Tag 5, Child 1 to Child 4, and the years 2022, 2023, 2024 when they should be values. In a database these repeating columns are replaced by a child table. This post shows the move in SQL, pandas and Power Query, with the checks that confirm nothing was lost. It is a spoke of when a spreadsheet has become a database.

What is wrong with repeating columns

A person has any number of phones. The sheet pretends the number is at most three. The consequences are concrete.

• A fourth phone has nowhere to go. Someone squeezes two numbers into one cell, which breaks searching.

• Searching is awkward. WHERE phone1 = x OR phone2 = x OR phone3 = x is three conditions that grow every time a column is added.

• Counting is wrong. How many phone numbers do we hold? Counting non-blank cells across columns needs a special formula.

• Constraints cannot work. You cannot say "no number appears twice for a person" across three columns with a simple rule.

• The structure is wasted. Most of Phone 3 is null.

The relationship is one-to-many: one person, many phones. The relational answer is a child table where each phone is a row, and a foreign key points to the person.

The target

For a people table with a key id:

The child table person_phones:

Cy has no phones and no rows. The position column keeps the order of the original columns. Whether you need it depends on whether the order means something: in many sheets the first phone is the preferred one, and discarding the order loses that.

CREATE TABLE person_phones (
    person_id integer NOT NULL REFERENCES people(id) ON DELETE CASCADE,
    position  smallint NOT NULL CHECK (position > 0),
    phone     text NOT NULL,
    PRIMARY KEY (person_id, position),
    UNIQUE (person_id, phone)
);

The primary key on (person_id, position) stops two phones in the same slot, and the unique constraint stops the same number twice. NOT NULL on phone means a blank has no row.

Unpivot in PostgreSQL

Use a lateral join to a VALUES list: each person row is joined to a small table of its own phone columns, then blanks are dropped.

INSERT INTO person_phones (person_id, position, phone)
SELECT p.id, v.position, btrim(v.phone)
FROM people p
CROSS JOIN LATERAL (VALUES
    (1, p.phone_1),
    (2, p.phone_2),
    (3, p.phone_3)
) AS v(position, phone)
WHERE NULLIF(btrim(v.phone), '') IS NOT NULL;

The WHERE drops nulls and empty strings. Without it, you would insert rows with a null phone, which the NOT NULL constraint rejects loudly, and an empty string, which it accepts quietly. Include both in the filter. If duplicates are possible, add ON CONFLICT DO NOTHING and count what was skipped. After the load, drop the old columns:

ALTER TABLE people DROP COLUMN phone_1, DROP COLUMN phone_2, DROP COLUMN phone_3;

Do that only after verifying the child table. See unpivoting columns back into rows in SQL for the same pattern in SQL Server (CROSS APPLY), MySQL (UNION ALL) and DuckDB (UNPIVOT).

Unpivot in pandas

melt does the same for a DataFrame:

import pandas as pd

people = pd.read_excel("people.xlsx", dtype=str)
phones = people.melt(
    id_vars=["id"],
    value_vars=["phone_1", "phone_2", "phone_3"],
    var_name="slot",
    value_name="phone",
)
phones["position"] = phones["slot"].str.extract(r"(\d+)").astype(int)
phones["phone"] = phones["phone"].str.strip()
phones = phones[phones["phone"].notna() & (phones["phone"] != "")]
phones = phones[["id", "position", "phone"]].rename(columns={"id": "person_id"})
phones = phones.sort_values(["person_id", "position"]).reset_index(drop=True)

melt produces one row for every person and column combination (nine rows here), and the filter keeps the three that have a phone. Extracting the digit from the column name recovers the position. Read the sheet as text so that phone numbers keep their leading zeros (keeping leading zeros in Excel). Then load phones with to_sql or COPY.

If the columns are named irregularly, pass the list explicitly. A wildcard such as people.filter(like="phone_") is convenient and picks up an unrelated column such as phone_verified if one is added.

Unpivot in Power Query

Select the id column, then use Transform, Unpivot Other Columns on the others, or select the three phone columns and choose Unpivot Columns. Rename Attribute to slot and Value to phone. Null cells are dropped by Unpivot automatically. Extract the number from slot with Transform, Extract, Text After Delimiter, then set the type:

Unpivoted = Table.UnpivotOtherColumns(People, {"id"}, "slot", "phone"),
WithPos = Table.AddColumn(Unpivoted, "position", each Number.FromText(Text.AfterDelimiter([slot], "_")), Int64.Type),
Result = Table.RemoveColumns(WithPos, {"slot"})

This is the natural tool when the cleaning happens in Excel before loading (unpivoting columns into rows with Power Query). The result goes to a CSV or straight to the database.

Check the result

The expected number of child rows equals the number of non-blank cells in the original columns. Compute both:

SELECT
  (SELECT COUNT(*) FROM person_phones) AS child_rows,
  (SELECT COUNT(phone_1) + COUNT(phone_2) + COUNT(phone_3) FROM people_old) AS filled_cells;

COUNT(column) counts non-null values, so the sum is the number of filled cells (if blanks are nulls, not empty strings; normalize first). The two numbers must be equal, less any duplicates you deliberately skipped. Also check each person: the number of child rows for person 1 should be 2. Spot-check five people by eye against the sheet. Check that no person appears in the child table who is absent from people, which the foreign key guarantees.

A rebuild test completes the proof: pivot the child table back to three columns and compare it with the original sheet (pivoting rows into columns in PostgreSQL).

Other shapes of the same problem

Numbered groups of several columns. Child 1 name, Child 1 age, Child 2 name, Child 2 age. Each group is a row with several fields. Unpivot with a VALUES list of tuples: (1, p.child1_name, p.child1_age), (2, p.child2_name, p.child2_age).

Years or months as columns. 2022, 2023, 2024 hold the same measure for different periods. The child table has person_id, year and value. The column name becomes data.

Several values in one cell. red, blue, green in a tags column. Split on the delimiter into rows with string_to_table in PostgreSQL 14 or later (regexp_split_to_table in older versions), trimming each piece:

INSERT INTO item_tags (item_id, tag)
SELECT i.id, btrim(t.tag)
FROM items i
CROSS JOIN LATERAL string_to_table(i.tags, ',') AS t(tag)
WHERE btrim(t.tag) <> '';

A lookup instead of text. When the repeated value comes from a fixed list (a skill, a language), the child table holds a reference to a lookup table, not the text. This also makes spelling variants impossible once the references are in place.

Keep the order if it means something

If the sheet's first phone was the preferred contact, a position column keeps that. Some designs prefer an explicit flag, is_primary boolean, with a partial unique index so a person has at most one primary:

CREATE UNIQUE INDEX one_primary_phone ON person_phones (person_id) WHERE is_primary;

A flag says what you mean more clearly than "position 1". Decide with the people who use the data, and convert position 1 to the flag during the load.

When repeating columns are the right design

The set is fixed and small. A table of quarterly targets with exactly q1, q2, q3, q4 is a fixed structure, and four columns are fine. The test is whether a fifth value can ever exist.

It is a reporting table. A wide layout built from the normalized tables for a report is a presentation choice. Keep the long form as the stored data.

The columns are different things. home_phone and work_phone are named roles, not repetitions. Keep them as columns or turn the role into a column in a child table, depending on how many roles exist.

Performance depends on it. Rarely, a very wide, read-only table beats a join. Measure before choosing.

See PostgreSQL's pages on LATERAL and string_to_table, and the pandas reference for melt. To convert a small extract, use the CSV to JSON converter.