表结构:
TB
部门编号 部门 姓名 工号 员工月薪 岗位补助 奖金
D001 IT A S1001 $200.00 $200.00 $200.00
D002 Account B S1002 $200.00 $200.00 $200.00
D002 Account C S1003 $200.00 $200.00 $200.00
D003 HR D S1004 $200.00 $200.00 $200.00 怎样得到下面的表?
Department:D001 IT 员工月薪 岗位补助 奖金
S1001 A $200.00 $200.00 $200.00
Department:D002 Account 员工月薪 岗位补助 奖金
S1002 B $200.00 $200.00 $200.00
S1003 C $200.00 $200.00 $200.00
Department:D003 HR 员工月薪 岗位补助 奖金
S1004 D $200.00 $200.00 $200.00
谢谢了!
TB
部门编号 部门 姓名 工号 员工月薪 岗位补助 奖金
D001 IT A S1001 $200.00 $200.00 $200.00
D002 Account B S1002 $200.00 $200.00 $200.00
D002 Account C S1003 $200.00 $200.00 $200.00
D003 HR D S1004 $200.00 $200.00 $200.00 怎样得到下面的表?
Department:D001 IT 员工月薪 岗位补助 奖金
S1001 A $200.00 $200.00 $200.00
Department:D002 Account 员工月薪 岗位补助 奖金
S1002 B $200.00 $200.00 $200.00
S1003 C $200.00 $200.00 $200.00
Department:D003 HR 员工月薪 岗位补助 奖金
S1004 D $200.00 $200.00 $200.00
谢谢了!
insert into TB
select 'D001','IT','A','S1001',200,200,200 union all
select 'D002','Acc','B','S1002',200,200,200 union all
select 'D002','Acc','C','S1003',200,200,200 union all
select 'D003','HR','D','S1004',200,200,200 select 部分編號,部門,月薪,補助,獎金
from
(
select distinct 'Department:'+部分編號 as [部分編號],部門,'月薪' as [月薪] ,'補助' as [補助],'獎金' as [獎金],
tmp=(select count(*) from TB where 部分編號<=A.部分編號 )
from TB A
union all
select 工號,工號,rtrim(月薪),rtrim(補助),rtrim(獎金),
tmp=(select count(*) from TB where 部分編號<=B.部分編號 )
from TB B
) T
order by tmp,isnumeric(獎金)/*
Department:D001 IT 月薪 補助 獎金
S1001 A 200 200 200
Department:D002 Acc 月薪 補助 獎金
S1002 B 200 200 200
S1003 C 200 200 200
Department:D003 HR 月薪 補助 獎金
S1004 D 200 200 200*/drop table TB
insert xyzx select 'D001', 'IT', 'A', 'S1001', $200.00, $200.00, $200.00
union all select 'D002', 'Account', 'B', 'S1002', $200.00, $200.00, $200.00
union all select 'D002', 'Account', 'C', 'S1003', $200.00, $200.00, $200.00
union all select 'D003', 'HR', 'D', 'S1004', $200.00, $200.00, $200.00
go
declare @a varchar(100),@b varchar(100),@i int,@t varchar(20)
declare @sql varchar(200)set @i=1
declare cur cursor for select distinct 部门编号,部门 from xyzx
open cur
fetch next from cur into @a,@b
while @@fetch_status=0
begin
set @sql='create table Depart'+ltrim(@i)+'([Department:'+@a+'] varchar(20),['+@b+'] varchar(20),员工月薪 money,岗位补助 money,奖金 money)'
set @t='Depart'+ltrim(@i)
if object_id(@t) is not null
exec('drop table '+@t)
exec(@sql)
exec('insert '+@t+' select 工号,姓名,员工月薪,岗位补助,奖金 from xyzx where 部门编号='''+@a+''' and 部门='''+@b+'''')
fetch next from cur into @a,@b
set @i=@i+1
end
close cur
deallocate curgo
select * from depart1;select * from depart2;select * from depart3;
INSERT TB
SELECT 'D001','IT','A','S1001',200,200,200
UNION SELECT 'D002','Account','B','S1002',200.00,200.00,200.00
UNION SELECT 'D002','Account','C','S1003',200.00,200.00,200.00
UNION SELECT 'D003','HR','D','S1004',200.00,200.00,200.00
SELECT NO,DEPT,C1,C2,C3 FROM (
SELECT 部门编号,NO='Department:'+部门编号,DEPT=MAX(部门),C1='员工月薪',C2='岗位补助',C3='奖金' FROM TB GROUP BY 部门编号
UNION
SELECT 部门编号,工号,姓名,C1='$'+RTRIM(员工月薪),C2='$'+RTRIM(岗位补助),C3='$'+RTRIM(奖金) FROM TB ) T
ORDER BY 部门编号DROP TABLE TB/*
NO DEPT C1 C2 C3
--------------------- ---------- ------------- ------------- -------------
Department:D001 IT 员工月薪 岗位补助 奖金
S1001 A $200 $200 $200
Department:D002 Account 员工月薪 岗位补助 奖金
S1002 B $200 $200 $200
S1003 C $200 $200 $200
Department:D003 HR 员工月薪 岗位补助 奖金
S1004 D $200 $200 $200(7 row(s) affected)*/
也感谢chuifengde,
结贴了!
你们都是牛人啊!