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

来源：互联网发布：比斯利在雄鹿的数据编辑：程序博客网时间：2024/06/06 15:54

--(部门收入)横表

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