sqlserver pivot 动态行转列多行转多列

先看效果：

原来的查询结果

 想要的效果：

用到的关键函数：  

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