You need to
unpivot[
^] data!
Check this:
DECLARE @tablea TABLE(
ID INT, [Name] VARCHAR(30),
YTDLVL1 INT, YTDLVL2 INT,
YTDLVL3 INT, QLVL1MT INT,
QLVL1SC INT, QLVL1SP INT,
QLVL2MT INT, QLVL2SC INT,
QLVL2SP INT, QLVL2EN INT)
INSERT INTO @tablea (ID, [Name], YTDLVL1, YTDLVL2, YTDLVL3, QLVL1MT, QLVL1SC, QLVL1SP, QLVL2MT, QLVL2SC, QLVL2SP, QLVL2EN)
VALUES(1, 'John', 100, 60, 90, 85, NULL, NULL, NULL, NULL, 20, NULL),
(1, 'John', 100, 60, 90, 79, NULL, NULL, NULL, NULL, 90, NULL),
(1, 'John', 100, 60, 90, NULL, NULL, 55, NULL, 66, NULL, NULL)
SELECT ID, [NAME], CASE
WHEN Program Like '%[1]%' THEN 'LVL1'
WHEN Program Like '%[2]%' THEN 'LVL2'
WHEN Program Like '%[3]%' THEN 'LVL3'
END AS Program,
CASE
WHEN Program Like 'YTD%' THEN 'YTD'
WHEN Program Like '%MT' THEN 'MT'
WHEN Program Like '%SP' THEN 'SP'
WHEN Program Like '%SC' THEN 'SC'
END AS Category, Marks
FROM (
SELECT *
FROM @tablea
) AS a
UNPIVOT (Marks FOR Program IN (YTDLVL1, YTDLVL2, YTDLVL3, QLVL1MT, QLVL1SC, QLVL1SP, QLVL2MT, QLVL2SC, QLVL2SP, QLVL2EN)) AS unpvt
Reslut:
ID NAME Program Category Marks
1 John LVL1 YTD 100
1 John LVL2 YTD 60
1 John LVL3 YTD 90
1 John LVL1 MT 85
1 John LVL2 SP 20
1 John LVL1 YTD 100
1 John LVL2 YTD 60
1 John LVL3 YTD 90
1 John LVL1 MT 79
1 John LVL2 SP 90
1 John LVL1 YTD 100
1 John LVL2 YTD 60
1 John LVL3 YTD 90
1 John LVL1 SP 55
1 John LVL2 SC 66
I hope that is what you wanted to achieve.
For further details, please see:
Unpivot Transformation[
^]