FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

WITH Tally(n) AS (
    SELECT TOP 50 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_objects  -- Any sufficiently large source
),
InputData AS (
    SELECT *
    FROM (
        VALUES
        (1, 'John',  'Doe',     'projA;projB;projC',  '20150701,20150801;20150901'),
        (2, 'Jane',  'Smith',   'projD;projC',        '20150701;20150902'),
        (3, 'Lisa',  'Anderson','projB;projC',        NULL),
        (4, 'Nancy', 'Johnson', 'projB;projC;projE',  '20150601,20150822,20150904'),
        (5, 'Chris', 'Edwards', 'projA',              '20150905')
    ) t(ID, fname, lname, projects, projdates)
),
SplitProjects AS (
    SELECT
        i.ID, i.fname, i.lname,
        TRIM(value) AS project,
        rn
    FROM InputData i
    CROSS APPLY (
        SELECT
            rn = ROW_NUMBER() OVER (ORDER BY n),
            value = LTRIM(RTRIM(
                value
            ))
        FROM Tally n
        CROSS APPLY (
            SELECT 
                TRY_CAST(
                    PARSENAME(REPLACE(REPLACE(i.projects, ';', '.'), ',', '.'), n.n)
                    AS NVARCHAR(MAX)
                ) AS value
        ) v
        WHERE value IS NOT NULL
    ) AS split
),
SplitProjDates AS (
    SELECT
        i.ID,
        rn,
        projdate = TRIM(value)
    FROM InputData i
    CROSS APPLY (
        SELECT
            rn = ROW_NUMBER() OVER (ORDER BY n),
            value = LTRIM(RTRIM(
                value
            ))
        FROM Tally n
        CROSS APPLY (
            SELECT 
                TRY_CAST(
                    PARSENAME(REPLACE(REPLACE(i.projdates, ';', '.'), ',', '.'), n.n)
                    AS NVARCHAR(MAX)
                ) AS value
        ) v
        WHERE i.projdates IS NOT NULL AND value IS NOT NULL
    ) AS split
)
SELECT 
    p.ID, p.fname, p.lname,
    p.project,
    d.projdate
FROM SplitProjects p
LEFT JOIN SplitProjDates d 
  ON p.ID = d.ID AND p.rn = d.rn
ORDER BY p.ID, p.rn;

ID	fname	lname	project	projdate
1	John	Doe	projC	20150901
1	John	Doe	projB	20150801
1	John	Doe	projA	20150701
2	Jane	Smith	projC	20150902
2	Jane	Smith	projD	20150701
3	Lisa	Anderson	projC	NULL
3	Lisa	Anderson	projB	NULL
4	Nancy	Johnson	projE	20150904
4	Nancy	Johnson	projC	20150822
4	Nancy	Johnson	projB	20150601
5	Chris	Edwards	projA	20150905

(11 rows affected)