Hi,
You can do it in this way:
CREATE TABLE #TempColors (Color1 VARCHAR(50), Color2 VARCHAR(50));
INSERT INTO #TempColors (Color1, Color2)
VALUES ('Green', NULL), ('Yellow', NULL), (NULL, 'Black'), (NULL, 'White'), ('Pink', NULL);
SELECT Color1, Color2 FROM #TempColors;
;WITH IDs_CTE
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID
FROM #TempColors),
Color1_CTE
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color1
FROM #TempColors
WHERE Color1 IS NOT NULL),
Color2_CTE
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color2
FROM #TempColors
WHERE Color2 IS NOT NULL)
SELECT Color1_CTE.Color1, Color2_CTE.Color2
FROM IDs_CTE
LEFT OUTER JOIN Color1_CTE
ON Color1_CTE.ID = IDs_CTE.ID
LEFT OUTER JOIN Color2_CTE
ON Color2_CTE.ID = IDs_CTE.ID
WHERE Color1_CTE.Color1 IS NOT NULL OR Color2_CTE.Color2 IS NOT NULL
DROP TABLE #TempColors;