2023年7月13日发(作者：)

SQLServer表名、字段名为变量时的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 intset @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+1end