Interview Question of the Week #011 – Script to Convert List to Table and Table to List

My first job interview was scheduled for 6 PM at a California company known for its early work in social media. Several candidates arrived. We all received the same two-hour assignment:

1. Convert a delimited list into table rows.
2. Convert the table rows back into a list.

Some candidates completed it. I did not. At that point I was not good enough at SQL scripting, and I failed the interview. When I got home, I wrote both solutions for practice so I would be ready the next time. Naturally, no interviewer ever asked me the same question again.

The original examples are still on the blog: list to table and table to comma-separated list. They are useful historical answers. Here is the shorter modern form I would discuss today.

Two directions, one order

On SQL Server 2022 or later, the third argument of STRING_SPLIT returns each element’s ordinal. Store that position explicitly. STRING_AGG, available from SQL Server 2017, uses WITHIN GROUP to put the values back in the intended order. Run this complete example on SQL Server 2022 or later:

DECLARE @Items table
(
    Position bigint NOT NULL PRIMARY KEY,
    Value nvarchar(50) NOT NULL
);

INSERT @Items (Position, Value)
SELECT ordinal, value
FROM STRING_SPLIT(N'red,blue,green', N',', 1);

SELECT Position, Value
FROM @Items
ORDER BY Position;

SELECT STRING_AGG(CONVERT(nvarchar(max), Value), N',')
       WITHIN GROUP (ORDER BY Position) AS ListBack
FROM @Items;

The first query returns individual rows in the list’s order. The second rebuilds the comma-separated text. Do not assume table rows have an inherent order, and do not rely on the split function’s output order without ORDER BY ordinal. For an older SQL Server version, the linked original approaches may help, but review their ordering and special-character behavior before using them in production.

A comma inside a value, an empty element, or a NULL needs a stated rule. STRING_SPLIT is not a complete quoted-file parser. In an interview I would first state the input contract, then show the two queries and their limitations. I learned that lesson after a two-hour failure; the script itself took less time to remember.

Can you guess the company where that interview took place?

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.