Hi there,
I hope this helps..
DECLARE @x TABLE (col1 INT, col2 VARCHAR(10))
INSERT INTO @x
SELECT 1,'0TY/OK' UNION ALL
SELECT 1,'0TY/OK' UNION ALL
SELECT 1,'0TY/OK' UNION ALL
SELECT 1,'0TY/OK' UNION ALL
SELECT 1,'0TY/OK' UNION ALL
SELECT 2,'2KP/L' UNION ALL
SELECT 2,'2KP/L' UNION ALL
SELECT 2,'2KP/L' UNION ALL
SELECT 2,'2KP/L' UNION ALL
SELECT 2,'2KP/L' UNION ALL
SELECT 3,'7U5/2M' UNION ALL
SELECT 3,'7U5/2M' UNION ALL
SELECT 3,'7U5/2M' UNION ALL
SELECT 3,'7U5/2M'
;WITH col1_and_col2 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY col1,col2) AS row_num, col1, col2
FROM @x
)
,col3 AS
(
SELECT col2, ROW_NUMBER() OVER (ORDER BY col1,col2) AS row_num
FROM @x
GROUP BY col1,col2
)
SELECT a.col1,a.col2,b.col2 AS col3
FROM col1_and_col2 a
LEFT JOIN col3 b on a.row_num=b.row_num
Cheers!