Table1如下:
姓名(name) 实发工资(SFGZ) 年份(Year) 月份(Month)
aaa 1000 2002 1
bbb 500 2002 1
bbb 700 2002 2
aaa 1358 2002 2
aaa 1888 2002 3
aaa 1208 2002 4
..............-----------------------------------------------------------------
现在想实现每个职工例如2002年每个月的实发工资,格式如下:
姓名(name) 1月 2月 3月 4月 5月 ....
aaa 1000 1358 1888 1208 .......
bbb 500 700 ...........................
.....................望高手给予赐教!
小弟先谢谢了。
姓名(name) 实发工资(SFGZ) 年份(Year) 月份(Month)
aaa 1000 2002 1
bbb 500 2002 1
bbb 700 2002 2
aaa 1358 2002 2
aaa 1888 2002 3
aaa 1208 2002 4
..............-----------------------------------------------------------------
现在想实现每个职工例如2002年每个月的实发工资,格式如下:
姓名(name) 1月 2月 3月 4月 5月 ....
aaa 1000 1358 1888 1208 .......
bbb 500 700 ...........................
.....................望高手给予赐教!
小弟先谢谢了。
姓名(name) 实发工资(SFGZ) 年份(Year) 月份(Month)
aaa 1000 2002 1
bbb 500 2002 1
bbb 700 2002 2
aaa 1358 2002 2
aaa 1888 2002 3
aaa 1208 2002 4
..............-----------------------------------------------------------------
现在想实现每个职工例如2002年每个月的实发工资,格式如下:
姓名(name) 1月 2月 3月 4月 5月 ....
aaa 1000 1358 1888 1208 .......
bbb 500 700 ...........................
.....................望高手给予赐教!
小弟先谢谢了。
这和fastreport,reportmachine没有关系的吧,我是问SQL语句的。
这是一个报表数据横打的问题,已经在上述问题中讨论过了,先转给你一个例子写一个存储过程,用第二个表的科目编号作临时表的字段,然后将表一数据填入临时表就可以了。 declare @sql varchar(255),@field varchar(20)
declare tt cursor for select distinct 月份 from 表2
open tt
fetch tt into @field
set @sql = ' create ##mm( 月份 varchar(10) '
while @@fetch_status=0
begin
set @sql = @sql + ','+@field+' varchar(12) '
fetch tt into @field
end
close tt
deallocate tt
set @sql = @sql+')'
exec(@sql)
然后将数据写入##mm
还有个更详细的例子CREATE PROCEDURE pPostBagReqBranchDayDetail
@dOperDate smalldatetime
AS
begin
--动态生成所有种类邮袋的请领报表
declare @nvcSqlString1 nvarchar(3000)
declare @nvcSqlString2 nvarchar(3000)
declare @nvcColumns nvarchar(20)
declare @nvcColumns2 nvarchar(20)
set @nvcSqlString1='create table ##tPostBagReqBranchDayDetail('+char(13)
set @nvcSqlString1=@nvcSqlString1+'BranchNo char(6)'+char(13)
begin DECLARE cDayDetail1 CURSOR FOR
SELECT cast(KindNo as nvarchar(20)) FROM PostBag.dbo.tPostBagKind
OPEN cDayDetail1
FETCH NEXT FROM cDayDetail1 into @nvcColumns
WHILE @@FETCH_STATUS = 0
BEGIN
set @nvcSqlString1=@nvcSqlString1+',Bag'+@nvcColumns+' smallint default 0 not null'+char(13) FETCH NEXT FROM cDayDetail1 into @nvcColumns
END
CLOSE cDayDetail1
DEALLOCATE cDayDetail1 set @nvcSqlString1=@nvcSqlString1+')' EXEC sp_executesql @nvcSqlString1
insert ##tPostBagReqBranchDayDetail (BranchNo)
select distinct BranchNo from tPostBagRequire where PostSys.dbo.fnDate(OperDate)=@dOperDate
end begin DECLARE cDayDetail2 CURSOR FOR
SELECT cast(KindNo as nvarchar(20)) FROM PostBag.dbo.tPostBagKind OPEN cDayDetail2 FETCH NEXT FROM cDayDetail2 into @nvcColumns2
WHILE @@FETCH_STATUS = 0
BEGIN
set @nvcSqlString2=''
set @nvcSqlString2='update a'+char(13)
set @nvcSqlString2=@nvcSqlString2+'set '+'Bag'+@nvcColumns2+'=b.TotalNumber'+char(13)
set @nvcSqlString2=@nvcSqlString2+'from ##tPostBagReqBranchDayDetail a'+char(13)
set @nvcSqlString2=@nvcSqlString2++',PostBag.dbo.fnReqGrpBranchDayKind('+''''+left(convert(nvarchar(50),@dOperDate,20),10)+''''+')'+' b '+char(13)
set @nvcSqlString2=@nvcSqlString2+'where a.BranchNo=b.BranchNo and b.KindNo='+@nvcColumns2 EXEC sp_executesql @nvcSqlString2
FETCH NEXT FROM cDayDetail2 into @nvcColumns2
END
CLOSE cDayDetail2
DEALLOCATE cDayDetail2
end
end
GOEXECUTE pPostBagReqBranchDayDetail '2002-7-17'
GO