SQL Server 表名、字段名为变量时的sql 查询

--(部门收入)横表

create table DepartmentMonthIncome

(

    NID uniqueidentifier not null primary key DEFAULT (newid()),

    DepartmentName varchar(50),

    [1] decimal,

    [2] decimal,

    [3] decimal,

    [4] decimal,

    [5] decimal,

    [6] decimal,

    [7] decimal,

    [8] decimal,

    [9] decimal,

    [10] decimal,

    [11] decimal,

    [12] decimal

)

--(部门收入)纵表

create table DepartmentMonthIncome2

(

    NID uniqueidentifier not null primary key DEFAULT (newid()),

    DepartmentName varchar(50),

    [Month] int,

    Income decimal

)

---插入横表测试数据

insert into DepartmentMonthIncome(DepartmentName,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

values('成都分公司',3500,4000,3000,5000,null,null,5000,6000,null,4000,7000,9000)

 

insert into DepartmentMonthIncome(DepartmentName,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

values('绵阳分公司',3500,4000,3000,5000,null,null,5000,6000,null,4000,7000,9000)

 

insert into DepartmentMonthIncome(DepartmentName,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

values('遂宁分公司',3500,4000,3000,5000,null,null,5000,6000,null,4000,7000,9000)

 

insert into DepartmentMonthIncome(DepartmentName,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

values('德阳分公司',3500,4000,3000,5000,null,null,5000,6000,null,4000,7000,9000)

 

--将横表数据转到纵表,列名变量我们采用字符串来进行拼接,在查询字符串中常量我们用变量来表示示例如下：

declare @index int

set @index=1

 

while(@index<=12)

begin

    declare @sql varchar(1000)

    set @sql='

    declare @Month int

    set @Month=convert(int,'+convert(varchar(2),@index)+')

    insert into DepartmentMonthIncome2(DepartmentName,[Month],Income)

    select DepartmentName,@Month,['+convert(varchar(2),@index)+'] from DepartmentMonthIncome'

    EXEC(@SQL)

    set @index=@index+1

end