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

先看效果:

原来的查询结果

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

 

 

 想要的效果:

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

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

上一篇:mysql 存储引擎分类和事务隔离级别


下一篇:linux mysql 配置