DECLARE @t TABLE (dssid1 VARCHAR(3), dssid2 VARCHAR(3), dssid3 VARCHAR(3), name1 VARCHAR(20), name2 VARCHAR(20), name3 VARCHAR(20))

INSERT INTO @t

SELECT '001', '003', '007', 'allan', 'john', 'bill' UNION ALL

SELECT '111', '010', '024', 'john', 'sam', 'oti'

SELECT dssid1, name1 FROM @t WHERE name1 IS NOT NULL UNION ALL

SELECT dssid2, name2 FROM @t WHERE name2 IS NOT NULL UNION ALL

SELECT dssid3, name3 FROM @t WHERE name3 IS NOT NULL