SQL通用行转列，动态列

本人能力有限，请大家复制后自行优化。

 

 

使用方法：

        select row_number() over(order by convert(date,inDate,105)) rowid,convert(date,inDate,105) 日期,
        sum(case when flag='二级报警' then 1 else 0 end ) 二级报警, 
        sum(case when flag='三级报警' then 1 else 0 end ) 三级报警 into ##tt --插入到全局临时表
        from pinAdd where inDate>='2020-11-30' 
        group by convert(date,inDate,105)

        select * from ##tt

        exec DynamicColumn @table='##tt',     --临时表名
                           @firstName='日期', --指定按哪一列来转换
                           @orderName='rowid' --指定排序列

        drop table ##tt --用完请将全局临时表删除

内部实现：

ALTER PROCEDURE [dbo].[DynamicColumn]
@table varchar(50), --临时表名
@firstName varchar(50), --按哪一列(列名)来转换
@orderName varchar(50) --按哪一列来排序
AS
begin
    declare @sql nvarchar(max),@sql2 nvarchar(max),@subStr varchar(500),@id int,@count int,@col varchar(50)

    set @sql='create table ##Dtb('+quotename(@firstName)+' varchar(50),'
    set @id=1
    set @subStr=''

    exec('select row_number()over(order by convert(varchar(50),'+@orderName+')) id,'+@firstName+' as '+@firstName+' into ##tb from tempdb..'+@table) 
    select @count=count(1) from ##tb
    while(@id<=@count)
    begin
        set @sql2='select @col='+@firstName+' from ##tb where id=@id'
        exec sp_executesql @sql2,N'@col varchar(50) out,@id int',@col out,@id 

        set @subStr=@subStr+quotename(@col)+' varchar(50),'

        set @id=@id+1
        
    end
    exec(@sql+@subStr+')')



    set @id=1
    select row_number()over(order by colid) id,name into #ins from tempdb.dbo.syscolumns where id=object_id( 'tempdb..'+@table) and name!=@firstName order by colid
    select @count=count(1) from #ins

    while(@id<=@count)
    begin
        select @col=name from #ins where id=@id

        set @sql ='insert into ##Dtb select '+quotename(@col,'''')+', '
        --select * from #ins for xml path('')
        
        --set @sql2='select @col=(
        --    select '+''''''''''+'+convert(varchar(10),['+@col+'])+'+quotename(''',','''')+' from '+@table+' order by '+@orderName+' for xml path('''')
        --)'

        set @sql2=concat('select @col=(select ''''''''+convert(varchar(10),[',@col,'])+' , ''''''','' from ',@table,' order by rowid for xml path(''''))')
    
        exec sp_executesql @sql2,N'@col varchar(max) out',@col out

        set @sql=@sql+substring(@col, 1, len(@col)-1)
        exec(@sql)

        set @id=@id+1
    end


    set @sql=N'select * from ##Dtb where '+quotename(@firstName)+'!=@orderName'
    exec sp_executesql @sql,N'@orderName varchar(50)',@orderName

    --set @sql='drop table '+@table
    --exec(@sql)
    drop table ##Dtb
    drop table ##tb
end