Convert a single column to multiple in SQL Server

Question

I have a single column of data in SQL Sever and need to expand this to multiple (2) columns.

The raw data looks like so:

Col1
-------
Monday
Jon
Boris
Natalie
Tuesday
James
Tom
Boris

I'd like it to look like this:

Name   | Day
-------+---------
Jon    | Monday
Boris  | Monday
Natalie| Monday
James  | Tuesday
Tom    | Tuesday
Boris  | Tuesday

Many thanks in advance.

Answer 1

Assuming you are going to be able to add that identity column to source table for ordering and that your table will look something like this:

CREATE TABLE Table1 (id int identity(1,1), Col1 NVARCHAR(50))

INSERT INTO Table1 (Col1)
VALUES 
    ('Monday'),('Jon'),('Boris'),('Natalie'),
    ('Tuesday'),('James'),('Tom'),('Boris')

You can try the following:

• First create a temp table for weekdays so you can distinguish them (can also be done in other ways)
• Join days back with original table looking for larger IDs (t.ID > d1.ID)
• Prevent those that should go in the next day ID (t.ID < MIN(d2.Id))
• Coalesce is there just to make sure it works for the last day in the list.

.

CREATE TABLE #weekDays (wd NVARCHAR(10));

INSERT INTO #weekDays
VALUES 
    ('Monday'), ('Tuesday'), ('Wednesday'), 
    ('Thursday'), ('Friday'), ('Saturday'), ('Sunday');

WITH CTE_Days AS
(
    SELECT t.*
    FROM Table1 t
    INNER JOIN #weekDays wd ON wd.wd = t.col1
)
SELECT *
FROM CTE_Days d1
INNER JOIN Table1 t ON t.id > d1.id AND t.id < 
    COALESCE((
        SELECT MIN(d2.id)
        FROM CTE_Days d2
        WHERE d2.id > d1.id
    ), t.id + 1)