Increment value in SQL SELECT statement

Question

Here is a sample code for what I'm trying to do, and below is the result:

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 

Result:

 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 

I would like to receive:

 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 

It should increment the number in NumRole for docContRole and docID (ex Author 1, Author 2, etc.). He currently gives the total number of authors per docID .

My ultimate goal is to get something like

  DocID Author_1 Author_2 Author_3 Staff_1 Staff_2 ResCoor_1 30 Smith Dolby Barny Daln Johny Sanny 31 Tammy 

Answer 1

You can try this

 select td.DocID, td.FullName, td.DocContRole, row_number() over (partition by td.DocID, td.DocContRole order by td.FullName) as NumRole from dbo.#TempDoc_DocContRoles as td 

So dynamic SQL will be like

SQL EXAMPLE FIDDLE

 create table #t2 ( DocID int, FullName nvarchar(max), NumRole nvarchar(max) ) declare @pivot_columns nvarchar(max), @stmt nvarchar(max) insert into #t2 select td.DocID, td.FullName, td.DocContRole + cast( row_number() over (partition by td.DocID, td.DocContRole order by td.FullName) as nvarchar(max)) as NumRole from t as td select @pivot_columns = isnull(@pivot_columns + ', ', '') + '[' + NumRole + ']' from (select distinct NumRole from #t2) as T select @stmt = ' select * from #t2 as t pivot ( min(FullName) for NumRole in (' + @pivot_columns + ') ) as PT' exec sp_executesql @stmt = @stmt