先看效果：
原来的查询结果
想要的效果：
用到的关键函数：
pivot() 快速实现行转列
PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]))
unpivot() 快速实现列传行 【作为扩展，此次不用】
UNPIVOT([转换为行的列值在转换后对应的列名] for [转换为行的列名在转换后对应的列名] in ([转换为行的列1],[转换为行的列2],[转换为行的列3],...[转换为行的列N]))
基础表创建及初始化：
/*==============================================================*/ /* Table: REJECT_NOTIFICATION */ /*==============================================================*/ create table REJECT_NOTIFICATION ( reject_id int identity, admin_id int null, table_id int null, table_name nvarchar(50) null, "key" nvarchar(50) not null, remark nvarchar(2000) null, create_date datetime null, constraint PK_REJECT_NOTIFICATION primary key (reject_id) ) go insert into REJECT_NOTIFICATION values(1,1,‘applications‘,‘name‘,‘姓名输入错误‘,getdate()) insert into REJECT_NOTIFICATION values(1,1,‘applications‘,‘phone‘,‘电话输入错误‘,getdate()) insert into REJECT_NOTIFICATION values(1,1,‘applications‘,‘address‘,‘地址输入错误‘,getdate())
首先写出符合结果的语句：
select max([name]) as [name_remark],max([phone]) as [phone_remark],max([address]) as [address_remark]
from
(
select [name],[phone],[address] from REJECT_NOTIFICATION a pivot (max(remark) for [key] in ([name],[phone],[address]) ) as pv
where table_id = 1 and table_name = ‘Applications‘
) s
然后观察规律，将固定格式的语句，变成动态拼接的sql语句，最后通过 exec(@sql)
获取结果：
declare @event nvarchar(max) = ‘‘ declare @sql nvarchar(max) = ‘‘ declare @queryexp nvarchar(max) = ‘‘ --获取内层替换变量 select @event = @event + ‘,[‘+[key]+‘]‘ from (select distinct [key] from REJECT_NOTIFICATION) a order by [key] --获取外层替换变量 select @queryexp = @queryexp + ‘,max([‘+[key]+‘]) as ‘+‘[‘+[key]+‘_remark]‘ from (select distinct [key] from REJECT_NOTIFICATION) a order by [key] --去掉第一个逗号 select @queryexp = right(@queryexp,len(@queryexp)-1) --去掉第一个逗号 select @event = right(@event,len(@event)-1) --组装最后的查询语句 set @sql=‘select ‘+ @queryexp + ‘ from( select ‘+ @event + ‘from REJECT_NOTIFICATION a pivot (max(remark) for [key] in (‘+ @event + ‘) ) as pv where table_id = ‘ + Convert(nvarchar,@table_id) + ‘ and table_name = ‘‘‘ + @table_name + ‘‘‘) s‘ print @sql --执行 exec(@sql)
如果要加ID或者其他字段，也可以尝试下：
比如需要把Table_Id加进来
select max([table_id]) as [table_id],max([name]) as [name_remark],max([phone]) as [phone_remark],max([photo]) as [photo_remark]
from(
select table_id,[name],[phone],[photo]from REJECT_NOTIFICATION a pivot (max(remark) for [key] in ([name],[phone],[photo]) ) as pv
where table_id = 1 and table_name = ‘Applications‘
) s
动态写法，参考上面的例子即可
declare @event nvarchar(max) = ‘table_id‘ declare @event2 nvarchar(max) = ‘‘ declare @sql nvarchar(max) = ‘‘ declare @queryexp nvarchar(max) = ‘max([table_id]) as [table_id]‘ --获取内层替换变量 select @event = @event + ‘,[‘+[key]+‘]‘,@event2 = @event2 + ‘,[‘+[key]+‘]‘ from (select distinct [key] from REJECT_NOTIFICATION) a order by [key] --获取外层替换变量 select @queryexp = @queryexp + ‘,max([‘+[key]+‘]) as ‘+‘[‘+[key]+‘_remark]‘ from (select distinct [key] from REJECT_NOTIFICATION) a order by [key] --去掉第一个逗号 --select @queryexp = right(@queryexp,len(@queryexp)-1) --去掉第一个逗号 select @event2 = right(@event2,len(@event2)-1) --组装最后的查询语句 set @sql=‘select ‘+ @queryexp + ‘ from( select ‘+ @event + ‘from REJECT_NOTIFICATION a pivot (max(remark) for [key] in (‘+ @event2 + ‘) ) as pv where table_id = ‘ + Convert(nvarchar,@table_id) + ‘ and table_name = ‘‘‘ + @table_name + ‘‘‘) s‘ print @sql --执行 exec(@sql)
参考：
https://blog.csdn.net/u010892506/article/details/89458862