Please read the first link in my signature for how to post data where we can use it easier. It's a short sample data set, so I did it for you this time.

This query provides the desired result:

WITH SampleData AS

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt.lastName, dt.firstName) AS RN

FROM (VALUES (1, 'Fred', 'Flintstone'),

(2, 'Barney', 'Rubble'),

(1, 'Wilma', 'Flintstone'),

(3, 'Betty', 'Rubble'),

(3, 'Bam Bam', 'Rubble'),

(3, 'Pebbles', 'Rubble')

) dt(id, firstName, lastName)

)

SELECT id,

MAX(CASE WHEN RN = 1 THEN firstName ELSE NULL END) AS [First Name 1],

MAX(CASE WHEN RN = 1 THEN lastName ELSE NULL END) AS [Last Name 1],

MAX(CASE WHEN RN = 2 THEN firstName ELSE NULL END) AS [First Name 2],

MAX(CASE WHEN RN = 2 THEN lastName ELSE NULL END) AS [Last Name 2],

MAX(CASE WHEN RN = 3 THEN firstName ELSE NULL END) AS [First Name 3],

MAX(CASE WHEN RN = 3 THEN lastName ELSE NULL END) AS [Last Name 3]

FROM SampleData

GROUP BY id;

Your post title implies that you want this dynamic, and this is just coded for the three values. Please see the Cross Tabs and Pivot Tables, Part 1 and Part 2 links in my signature. Part 2 covers dynamic pivots.