try this:
WITH Data
AS ( SELECT *
FROM ( VALUES
( 1, 11, 'Baseball', 'Varsity', 'Baseball', 'Recreational'),
( 2, 22, 'Baseball,Basketball', 'Varsity,Junior Varsity', 'Baseball', 'Varsity'),
( 3, 33, 'Soccer', 'Varsity', 'Soccer,Track & Field', 'Recreational,Intramural'),
( 4, 44, NULL , NULL , 'Tennis', 'Varsity'),
( 5, 55, 'Volleyball', 'Varsity', NULL , NULL ),
( 6, 66, 'Baseball,Basketball', 'Varsity,Varsity', 'Soccer,Football', 'Varsity,Varsity'),
( 7, 77, 'Baseball,Basketball,Rowing', 'Varsity,Varsity,Varsity', 'Soccer,Football,Volleyball', 'Varsity,Varsity,Recreational') )
AS T ( id, userid, sport1, sportlevel1, sport2, sportlevel2 )
),
Tally
AS ( SELECT n = 1
UNION ALL
SELECT n + 1
FROM Tally
WHERE n <= 100
),
Sprt1
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sport1) = 0 THEN sport1
ELSE SUBSTRING(sport1, T.n,
CHARINDEX(',',
SUBSTRING(sport1 + ',',
t.n, 100)) - 1)
END AS sport1
FROM data AS d
JOIN Tally AS T ON COALESCE(LEN(d.sport1), 1) >= t.n
AND SUBSTRING(','
+ COALESCE(d.sport1,
''), n, 1) = ','
),
lvl1
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sportlevel1) = 0
THEN sportlevel1
ELSE SUBSTRING(sportlevel1, T.n,
CHARINDEX(',',
SUBSTRING(sportlevel1
+ ',', t.n, 100))
- 1)
END AS sportlevel1
FROM data AS d
JOIN Tally AS T ON COALESCE(LEN(d.sportlevel1), 1) >= t.n
AND SUBSTRING(','
+ COALESCE(d.sportlevel1,
''), n, 1) = ','
),
sprt2
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sport2) = 0 THEN sport2
ELSE SUBSTRING(sport2, T.n,
CHARINDEX(',',
SUBSTRING(sport2 + ',',
t.n, 100)) - 1)
END AS sport2
FROM data AS d
JOIN Tally AS T ON COALESCE(LEN(d.sport2), 1) >= t.n
AND SUBSTRING(','
+ COALESCE(d.sport2,
''), n, 1) = ','
),
lvl2
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sportlevel2) = 0
THEN sportlevel2
ELSE SUBSTRING(sportlevel2, T.n,
CHARINDEX(',',
SUBSTRING(sportlevel2
+ ',', t.n, 100))
- 1)
END AS sportlevel2
FROM data AS d
JOIN Tally AS T ON COALESCE(LEN(d.sportlevel2), 1) >= t.n
AND SUBSTRING(','
+ COALESCE(d.sportlevel2,
''), n, 1) = ','
),
final
AS ( SELECT COALESCE(sprt1.RN, lvl1.RN, sprt2.rn, lvl2.rn) AS RN ,
COALESCE(sprt1.id, lvl1.id, sprt2.id, lvl2.id) AS id ,
COALESCE(sprt1.userid, lvl1.userid, sprt2.userid,
lvl2.userid) AS userid ,
sprt1.sport1 ,
lvl1.sportlevel1 ,
sprt2.sport2 ,
lvl2.sportlevel2
FROM sprt1
FULL JOIN lvl1 ON sprt1.id = lvl1.id
AND sprt1.RN = lvl1.RN
FULL JOIN sprt2 ON COALESCE(sprt1.id, lvl1.id) = sprt2.id
AND COALESCE(sprt1.RN, lvl1.RN) = sprt2.RN
FULL JOIN lvl2 ON COALESCE(sprt1.id, lvl1.id, sprt2.id) = lvl2.id
AND COALESCE(sprt1.RN, lvl1.RN,
sprt2.rn) = lvl2.RN
)
SELECT CONVERT(VARCHAR, id) + '_'
+ CONVERT(VARCHAR, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY userid , part , sport )) AS id ,
userid ,
sport ,
sportlevel1 ,
sportlevel2
FROM ( SELECT 1 part ,
id ,
userid ,
sport1 AS sport ,
sportlevel1 ,
CASE WHEN sport1 = sport2 THEN sportlevel2
ELSE NULL
END sportlevel2
FROM final
WHERE sport1 IS NOT NULL
UNION ALL
SELECT 2 ,
id ,
userid ,
sport2 ,
NULL ,
sportlevel2
FROM final
WHERE sport2 IS NOT NULL
AND sport2 != COALESCE(sport1, '')
) FinalDataset
OPTION ( MAXRECURSION 1000 )
output
code for stored procedure
--Create demo temp table for testing
IF OBJECT_ID('Tempdb..#Data') IS NOT NULL
DROP TABLE #Data
SELECT *
INTO #Data
FROM ( VALUES ( 1, 11, 'Baseball', 'Varsity', 'Baseball', 'Recreational'),
( 2, 22, 'Baseball,Basketball', 'Varsity,Junior Varsity', 'Baseball', 'Varsity'),
( 3, 33, 'Soccer', 'Varsity', 'Soccer,Track & Field', 'Recreational,Intramural'),
( 4, 44, NULL , NULL , 'Tennis', 'Varsity'),
( 5, 55, 'Volleyball', 'Varsity', NULL , NULL ),
( 6, 66, 'Baseball,Basketball', 'Varsity,Varsity', 'Soccer,Football', 'Varsity,Varsity'),
( 7, 77, 'Baseball,Basketball,Rowing', 'Varsity,Varsity,Varsity', 'Soccer,Football,Volleyball', 'Varsity,Varsity,Recreational') )
AS T ( id, userid, sport1, sportlevel1, sport2, sportlevel2 );
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--- CODE BELOW CAN BE USED THRO STORED PROCEDURE, VIEW DOES NOT ALLOW TEMP TABLE USAGE ---
----------------------------------------------------------------------------------------------------
--Create temp table with sequence num
IF OBJECT_ID('Tempdb..#Tally') IS NOT NULL --<<<~this code not required in Stored procedure, can be deleted
DROP TABLE #Tally --<<<~this code not required in Stored procedure, can be deleted
CREATE TABLE #Tally ( N INT PRIMARY KEY )
DECLARE @i INT = 1
WHILE @i < 1000
BEGIN
INSERT INTO #Tally
SELECT @i
SET @i = @i + 1
END
----------------------------------------------------------------------------------------------------
--split sport2 field and create temp table for final result
IF OBJECT_ID('Tempdb..#sprt1') IS NOT NULL
DROP TABLE #sprt1
SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sport1) = 0 THEN sport1
ELSE SUBSTRING(sport1, T.n,
CHARINDEX(',', SUBSTRING(sport1 + ',', t.n, 100))
- 1)
END AS sport1
INTO #sprt1
FROM #data AS d
JOIN #Tally AS T ON COALESCE(LEN(d.sport1), 1) >= t.n
AND SUBSTRING(',' + COALESCE(d.sport1, ''), n, 1) = ','
----------------------------------------------------------------------------------------------------
--split sportlevel1 field and create temp table for final result
IF OBJECT_ID('Tempdb..#lvl1') IS NOT NULL
DROP TABLE #lvl1
SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sportlevel1) = 0 THEN sportlevel1
ELSE SUBSTRING(sportlevel1, T.n,
CHARINDEX(',',
SUBSTRING(sportlevel1 + ',', t.n, 100))
- 1)
END AS sportlevel1
INTO #lvl1
FROM #data AS d
JOIN #Tally AS T ON COALESCE(LEN(d.sportlevel1), 1) >= t.n
AND SUBSTRING(',' + COALESCE(d.sportlevel1, ''), n,
1) = ','
----------------------------------------------------------------------------------------------------
--split sport2 field and create temp table for final result
IF OBJECT_ID('Tempdb..#sprt2') IS NOT NULL
DROP TABLE #sprt2
SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sport2) = 0 THEN sport2
ELSE SUBSTRING(sport2, T.n,
CHARINDEX(',', SUBSTRING(sport2 + ',', t.n, 100))
- 1)
END AS sport2
INTO #sprt2
FROM #data AS d
JOIN #Tally AS T ON COALESCE(LEN(d.sport2), 1) >= t.n
AND SUBSTRING(',' + COALESCE(d.sport2, ''), n, 1) = ','
----------------------------------------------------------------------------------------------------
--split sportlevel2 field and create temp table for final result
IF OBJECT_ID('Tempdb..#lvl2') IS NOT NULL
DROP TABLE #lvl2
SELECT ROW_NUMBER() OVER ( PARTITION BY Id, userid ORDER BY Id, userid , T.n ) AS RN ,
id ,
userid ,
CASE WHEN CHARINDEX(',', sportlevel2) = 0 THEN sportlevel2
ELSE SUBSTRING(sportlevel2, T.n,
CHARINDEX(',',
SUBSTRING(sportlevel2 + ',', t.n, 100))
- 1)
END AS sportlevel2
INTO #lvl2
FROM #data AS d
JOIN #Tally AS T ON COALESCE(LEN(d.sportlevel2), 1) >= t.n
AND SUBSTRING(',' + COALESCE(d.sportlevel2, ''), n,
1) = ','
----------------------------------------------------------------------------------------------------
--final data set
IF OBJECT_ID('Tempdb..#Final') IS NOT NULL
DROP TABLE #Final
SELECT COALESCE(S1.RN, L1.RN, S2.rn, L2.rn) AS RN ,
COALESCE(S1.id, L1.id, S2.id, L2.id) AS id ,
COALESCE(S1.userid, L1.userid, S2.userid, L2.userid) AS userid ,
S1.sport1 ,
L1.sportlevel1 ,
S2.sport2 ,
L2.sportlevel2
INTO #Final
FROM #sprt1 AS S1
FULL JOIN #lvl1 AS L1 ON S1.id = L1.id
AND S1.RN = L1.RN
FULL JOIN #sprt2 AS S2 ON COALESCE(S1.id, L1.id) = S2.id
AND COALESCE(S1.RN, L1.RN) = S2.RN
FULL JOIN #lvl2 AS L2 ON COALESCE(S1.id, L1.id, S2.id) = L2.id
AND COALESCE(S1.RN, L1.RN, S2.rn) = L2.RN
----------------------------------------------------------------------------------------------------
--Final output query
SELECT CONVERT(VARCHAR, id) + '_'
+ CONVERT(VARCHAR, ROW_NUMBER() OVER ( PARTITION BY id ORDER BY userid , part , sport )) AS id ,
userid ,
sport ,
sportlevel1 ,
sportlevel2
FROM ( SELECT 1 part ,
id ,
userid ,
sport1 AS sport ,
sportlevel1 ,
CASE WHEN sport1 = sport2 THEN sportlevel2
ELSE NULL
END sportlevel2
FROM #final
WHERE sport1 IS NOT NULL
UNION ALL
SELECT 2 ,
id ,
userid ,
sport2 ,
NULL ,
sportlevel2
FROM #final
WHERE sport2 IS NOT NULL
AND sport2 != COALESCE(sport1, '')
) FinalDataset
