使用动态SQL语句进行数据透视

您可以使用动态枢轴：

create table dbo.test([Name] Varchar(32), Code Varchar(30))insert into dbo.test values   ('A-1', 'A-One'), ('A 2', 'A-Two'), ('B 1-b', 'B-One'), ('B', 'A-Two'), ('C', 'A-One'), ('C', 'B-One'), ('C', 'C-One')declare @cols nvarchar(max)=''  --holds all the values that will become column names declare @alias nvarchar(max)='' --holds values that will become column names and converts values to 'x'declare @sql nvarchar(max)=''   --contains the TSQL dinamically generatedselect @cols = @cols + ', [' + [Name] + ']' from dbo.test group by [Name]select @alias = @alias + ', case when T.[' + [Name] + '] is not null then ''x'' else '''' end as [' + [Name] + ']'from dbo.test group by [Name]set @cols = RIGHt(@cols, len(@cols)-2) set @sql = @sql + ' select  T.Code ' + @alias + ' from ('set @sql = @sql + ' select piv.[Code], ' + @cols set @sql = @sql + ' from dbo.test ' set @sql = @sql + ' pivot (max([Name]) for [Name] in ('+@cols+') ) piv ' set @sql = @sql + ' ) T'--execute dynamic queryexec(@sql)

结果：