你可以试试这个
select td.DocID, td.FullName, td.DocContRole, row_number() over (partition by td.DocID, td.DocContRole order by td.FullName) as NumRolefrom dbo.#TempDoc_DocContRoles as td
所以动态SQL会像这样
SQL范例
create table #t2( DocID int, FullName nvarchar(max), NumRole nvarchar(max))declare @pivot_columns nvarchar(max), @stmt nvarchar(max)insert into #t2select td.DocID, td.FullName, td.DocContRole + cast( row_number() over (partition by td.DocID, td.DocContRole order by td.FullName) as nvarchar(max)) as NumRolefrom t as tdselect @pivot_columns = isnull(@pivot_columns + ', ', '') + '[' + NumRole + ']'from (select distinct NumRole from #t2) as Tselect @stmt = 'select *from #t2 as tpivot(min(FullName)for NumRole in (' + @pivot_columns + ')) as PT'exec sp_executesql @stmt = @stmt
