«Сводные» неагрегированные данные в SQL Server

это будет первый вопрос, который я разместил здесь, так что прошу прощения за любые непреднамеренные нарушения этикета доски.

В моем текущем проекте я взял большую ненормализованную таблицу и разбил ее на четыре отдельные нормализованные таблицы. Моя конечная цель, что я m обращается к этой доске, чтобы создать представление, которое имитирует ненормализованную таблицу для обратной совместимости.

Чтобы предоставить упрощенный снимок моего сценария, суть того, что я пытаюсь сделать, заключается в двух таблицах:

ASSOC_ROLE          ASSOCIATE
----------          ----------
assoc_id (fk)       assoc_id (pk)
role_id  (fk)       last_name
org_nbr  (fk)

Итак, если я введу следующий запрос ...

SELECT Assoc_Role.org_nbr, Assoc_Role.assoc_id, Associate.last_name, Assoc_Role.role_id
FROM Assoc_Role INNER JOIN
     Associate ON Assoc_Role.assoc_id = Associate.assoc_id
WHERE Assoc_Role.org_nbr = '1AA'

... я получу следующий набор результатов

org_nbr     assoc_id     last_name     role_id
-------     --------     ---------     -------
1AA         1447         Cooper        1
1AA         1448         Collins       3
1AA         1448         Collins       4
1AA         1448         Collins       5
1AA         1449         Lynch         6

В конечном итоге представление, которое я хотел бы построить, будет выглядеть примерно так:

org_nbr   role1_ID   role1_name   role2_ID   role2_name   role3_ID   role3_name   role4_ID   role4_name   role5_ID   role5_name   role6_ID   role6_name
-------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------
1AA       1447       Cooper       NULL       NULL         1448       Collins      1448       Collins      1448       Collins      1449       Lynch

Моя первоначальная мысль была чтобы попытаться использовать команду PIVOT, но я понимаю, что PIVOT требует некоторого агрегирования, и это не соответствует моему сценарию. Я также поиграл с командой CASE в предложении SELECT, но она не сводит мой результат к одной записи.

Надеюсь, кто-нибудь сможет пролить свет на то, как я могу это сделать. Дайте мне знать, если кому-то понадобится дополнительная информация. Спасибо!

Скотт