SELECT m.*,Agt_1,Agt_2,...,Agt_10
FROM
(
SELECT MEM_NBR,LN_NBR,
[1] AS Collateral_1,
[2] AS Collateral_2,
[3] AS Collateral_3,
...
[10] AS Collateral_10
FROM (SELECT MEM_NBR,LN_NBR,rn,ID FROM R) r1
PIVOT (MAX(ID) FOR rn IN ([1],[2],[3],..,[10]))p
)m
INNER JOIN
(
SELECT MEM_NBR,LN_NBR,
[1] AS Agt_1,
[2] AS Agt_2,
[3] AS Agt_3,
...
[10] AS Agt_10
FROM (SELECT MEM_NBR,LN_NBR,rn,AN FROM R) r2
PIVOT (MAX(AN) FOR rn IN ([1],[2],[3],..,[10]))q
)n
ON n.MEM_NBR = m.MEM_NBR
AND n.LN_NBR = m.LN_NBR

------------------------------------------------------------------------------------------------------

SQL Server MVP

http://visakhm.blogspot.com/