SQL 行列转换

摘自： http://t.zoukankan.com/yhnet-p-14768325.html

• 表

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)