SQL SELECT语句中的增量值

左开顺

这是我想要做的示例代码，下面是结果：
CREATE TABLE dbo.#TempDoc_DocContRoles (DocID int null, FullName varchar(500), DocContRole
   varchar (100), NumRole int null)
INSERT INTO #TempDoc_DocContRoles(DocID, FullName, DocContRole)
   SELECT
      d.DocID, c.FirstName + ' ' + c.LastName as FullName, ldcro.DocContRole
   FROM
      Document as d
   JOIN
      dbo.Split( ',','30,31') AS l ON d.DocID = cast(l.[Value] AS int)
   JOIN
      Doc_Contact AS dc ON d.DocID = dc.DocID
   JOIN
      Contact AS c ON dc.P_Number = c.P_Number
   LEFT JOIN
      lkpDocContactRole AS ldcro ON ldcro.DocContRoleID = dc.DocContRoleID
   JOIN
      dbo.Split( ',','1,2,7') AS r ON ldcro.DocContRoleID = cast(r.[Value] AS int)

CREATE TABLE dbo.#MaxNumRoles (DocID int null, DocContRole varchar(100), NumRole int null)
INSERT INTO  dbo.#MaxNumRoles (DocID,DocContRole,NumRole)
   SELECT
      DocID, DocContRole, COUNT(*)
   FROM
      dbo.#TempDoc_DocContRoles
   GROUP BY
      DocID, DocContRole
   HAVING
      Count(*) > 0
UPDATE td
SET td.NumRole = mr.NumRole
FROM dbo.#TempDoc_DocContRoles as td
INNER JOIN dbo.#MaxNumRoles as mr ON td.DocContRole = mr.docContRole
SELECT * FROM dbo.#TempDoc_DocContRoles
DROP TABLE dbo.#TempDoc_DocContRoles
DROP TABLE dbo.#MaxNumRoles
结果：
DocID FullName DocContRole NumRole
30    Smith Author       3
30    Daln    Staff       2
30    Dolby Author       3
31    Tammy Author       3
30    Barny Author       3
30    Sanny Res Coor    1
30    Johny Staff Rev    2
我想实际得到：
DocID FullName DocContRole NumRole
30    Smith Author       1
30    Daln    Staff       1
30    Dolby Author       2
31    Tammy Author       1
30    Barny Author       3
30    Sanny Res Coor    1
30    Johny Staff Rev    2
它应该NumRole按docContRole和增加数字docID（例如作者1，作者2等）。目前，它给出了每位作者的总数DocID。
我的最终目标是获得类似
   DocID Author_1  Author_2 Author_3 Staff_1 Staff_2 ResCoor_1
30             Smith    Dolby Barny Daln Johny Sanny
31             Tammy

解决方案:

SQL SELECT语句中的增量值

左开顺 LV1