then use this script i created:
1: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
2: * User : Gary
3: * Date : 20110616
4: * Description : Pivoting multiple rows into one row with multiple columns
5: *
6: * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
7:
8: --// Create example table with the usual All-American names
9:
10: CREATE TABLE #PivotRows (
11: FirstName VARCHAR(10)
12: , LastName VARCHAR(15)
13: , ModelOfInterest VARCHAR(15)
14: )
15:
16:
17: INSERT INTO
18: #PivotRows
19: SELECT 'John','Cunningham','CORSA' UNION ALL
20: SELECT 'John','Cunningham','ASTRA' UNION ALL
21: SELECT 'John','Cunningham','ANTARA' UNION ALL
22: SELECT 'Mick','Clarkson' ,'SIGNUM' UNION ALL
23: SELECT 'Mick','Clarkson' ,'AGILA' UNION ALL
24: SELECT 'Robert','Flanigan','INSIGNIA'
25:
26:
27:
28: SELECT * FROM #PivotRows
29:
30:
31: --// Shake and serve
32:
33: SELECT
34: FirstName
35: , LastName
36: , MAX(CASE WHEN rowid=1 THEN [ModelOfInterest] ELSE NULL END) AS ModelOfInterest1
37: , MAX(CASE WHEN rowid=2 THEN [ModelOfInterest] ELSE NULL END) AS ModelOfInterest2
38: , MAX(CASE WHEN rowid=3 THEN [ModelOfInterest] ELSE NULL END) AS ModelOfInterest3
39: , MAX(CASE WHEN rowid=4 THEN [ModelOfInterest] ELSE NULL END) AS ModelOfInterest4
40: FROM
41: (
42: SELECT
43: ROW_NUMBER() OVER ( PARTITION BY [FirstName],[LastName] ORDER BY [FirstName],[LastName] ) AS rowid
44: , *
45: FROM #PivotRows
46: ) t1
47: GROUP BY
48: FirstName
49: , LastName
