declare @Table AS TABLE
(
Col1 VARCHAR(100) ,
Col2 INT ,
Col3 VARCHAR(100)
)
INSERT @Table
( Col1, Col2, Col3 )
VALUES
( 'TEST', 30 ,'NY' ),
( 'TEST', 30 ,'CA' ),
( 'TEST2', 10 ,'TN' ),
( 'TEST2', 10 ,'TX' )
SELECT
xQ.Col1,
xQ.Col2,
MAX(CASE WHEN xQ.RowNumber = 1 THEN xQ.Col3 ELSE NULL END) AS Col3,
MAX(CASE WHEN xQ.RowNumber = 2 THEN xQ.Col3 ELSE NULL END) AS Col4
FROM
(
SELECT * , RANK() OVER(PARTITION BY T.Col1,T.Col2 ORDER BY T.Col1,T.Col2,T.Col3) AS RowNumber
FROM @Table AS T
)AS xQ
GROUP BY
xQ.Col1,
xQ.Col2