--(部门收入)横表
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