动态SQL语句根据数据库列来查询数据，动态的生成列名称

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



/******************************************************************************************
----------------------------------------------------------------------------------------
存储过程名称：  [contract_station_elec]
实现功能：    基准站月用电量统计报表
作者：      巫航

----------------------------------------------------------------------------------------
修改时间    版本号    修改人  修改摘要
2013年4月26日    V3.0    巫航    创建该存储过程，由于辽宁电信的需求，需要按各用电量电流值current_size来分割大小
----------------------------------------------------------------------------------------
******************************************************************************************/
alter PROCEDURE [dbo].[rpt_contract_station_elec]
  @station varchar(8000) = '',
  @time    varchar(8000) = '',
  @time_start datetime = null,
  @time_end   datetime = null,
  @sort       varchar(8000) = ''
as
begin
    set nocount on;

    declare @nv_table_name nvarchar(max); --表名称变量
    declare @nv_sql nvarchar(max);        --动态sql变量
    declare @v_station_kind int;
    declare @v_name varchar(30);
    declare @nv_column_name nvarchar(max);--SUM+列名称
    DECLARE @table VARCHAR(8000)
    DECLARE @sql nvarchar(max);
    
    SET @table = 'his_powermonth_' +
        CONVERT(VARCHAR(8000), DATEPART(yy, @time_start))
    --如果表不存在，使用没有数据的原始表
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @table + ']') AND type in (N'U'))
    BEGIN
        set @table = 'his_powermonth'
    END
    --创建临时表,放置局站
    DECLARE @station_where VARCHAR(8000)
    SET @station_where = ''
    IF @station<>''
    BEGIN
        IF object_id('tempdb..#TEMPTB1') is not null
            drop table #TEMPTB1
        CREATE table #TEMPTB1
        (
            station_sn VARCHAR(64)
        )
        CREATE clustered index #TEMPTB1_Index1 on #TEMPTB1 (station_sn)
        SET @sql = 'INSERT INTO #TEMPTB1 ' + @station
        EXEC(@sql)
        SET @station_where = 'INNER JOIN #TEMPTB1 tmp ON t.station_sn = tmp.station_sn'
    END
    

    --删除可能存在的临时表
    if object_id('tempdb..#temp_a') is not null
    begin
      drop table #temp_a;
    end
    --建立遥测量临时表
    CREATE TABLE #temp_a(
        [station_sn] varchar(64) not null,
        [current_size] tinyint,
        [value] real
    );
    SET @sql = 'INSERT INTO #temp_a([station_sn],[current_size],[value]) 
    select t.[station_sn],t.[current_size],sum(t.[value]) as [value] 
    FROM '+@table+' t ' + @station_where +' group by t.[current_size],t.[station_sn]'
    
    exec(@sql);
--    select * from #temp_a;
--    insert into #temp_a([station_sn],[am_kind],[value]) SELECT [station_sn],[am_kind],sum([value]) as [value] FROM [ems_his].[dbo].[his_powermonth_2013] 
--group by [am_kind],station_sn

    --删除可能存在的临时表
    if object_id('tempdb..#temp_table') is not null
    begin
      drop table #temp_table;
    end
    --建立电流值大小临时表
    CREATE TABLE #temp_table(
      [current_size] int not null
    );

    set @nv_table_name = ' a.*';
    set @nv_column_name = '';

    declare cs1 cursor for
    select [name] from [ems_cfg].[dbo].[cfg_currentsize]
    open cs1
    fetch next from cs1 into @v_name
    while @@fetch_status = 0
        begin
        set @nv_table_name = @nv_table_name + ' ,b.[' +@v_name+']*a.value as ['+@v_name +']';
        set @nv_column_name = @nv_column_name + ' ,ROUND(SUM(['+@v_name+']),2) as ['+@v_name+']'
        select @nv_sql = 'alter table #temp_table add [' + @v_name + '] int default 0';
        --print(@nv_sql);
        exec(@nv_sql);
        fetch next from cs1 into @v_name;
        end
    close cs1
    deallocate cs1

    insert into #temp_table(current_size)
    select current_size from [ems_cfg].[dbo].[cfg_currentsize]

    declare cs2 cursor for
    select [current_size],[name] from [ems_cfg].[dbo].[cfg_currentsize]
    open cs2
    fetch next from cs2 into @v_station_kind,@v_name
    while @@fetch_status = 0
    begin
        select @nv_sql = 'update #temp_table set [' + @v_name + '] = 1
         where [current_size] = ' + rtrim(@v_station_kind);
        print(@nv_sql);
        exec(@nv_sql);
        fetch next from cs2 into @v_station_kind,@v_name;
    end
    close cs2
    deallocate cs2

--  select [am_kind] from [ems_cfg].[dbo].[cfg_amkind];
--  select * from #temp_table;

    set @nv_table_name ='select s.station_name,l.lsc_name,p.precinct_name,'+ @nv_table_name + 
        ' from #temp_a a 
        left join #temp_table b on a.[current_size] = b.[current_size]
        left join ems_cfg.dbo.cfg_station s on s.station_sn=a.station_sn
        left join ems_cfg.dbo.cfg_lsc l on l.lsc_id=s.lsc_id
        left join ems_cfg.dbo.cfg_precinct p on p.lsc_id=l.lsc_id and p.precinct_id=s.precinct_id';
    set @nv_sql = 'select station_name,lsc_name,precinct_name'+@nv_column_name+' from ('+@nv_table_name+') t 
            group by station_name,lsc_name,precinct_name'
    print(@nv_sql);
    exec(@nv_sql);
    
    --查询出第二个表
    SELECT [current_size],[name] FROM [ems_cfg].[dbo].[cfg_currentsize]
end

经常遇到用户需要动态的查询每一列的统计数据，烦都烦死了，请教了DBA，帮忙花了点时间写了这个动态SQL，根据配置表的ID号，名称来动态生成数据表格和数据集

WEB上再根据名称来生成对应的中文列头