August 23, 2006 at 12:58 am
Dear All,
I have one table say Timesheet
values are as follows
job activity workfunction Hours comment
1 1 1 2 asa
2 1 1 3 sbsb
3 2 2 5 sdsa
4 2 3 6 fdgg
Now senario is that
I want to update Job from table timesheet (1 and 2 to 5) and (3 and 4 to 6).
Now it looks like follows;
job activity workfunction Hours comment
5 1 1 2 asa
5 1 1 3 sbsb
6 2 2 5 sdsa
6 2 3 6 fdgg
Insted of this I want
job activity workfunction Hours comment
5 1 1 5 asa,sbsb
6 2 2 11 sdsa,fdgg
Any solution for this.
Thanks In advance.
August 23, 2006 at 5:24 am
- Is this meaning you want your column "workfunction" to be a bitmap ?
check out the implications !!
- http://www.sqlservercentral.com/columnists/dpoole/usingbitstostoredata.asp
- you would need a function with cursor (or temptb) to concatenate the comments
Declare @OldJobID uniqueidentifier
Declare @StartJobId uniqueidentifier
Declare @Job_Id uniqueidentifier
Declare @step_id integer
Declare @step_name varchar(128)
Declare @NewComment varchar(8000)
select @NewComment = '', @OldJobID = newid()
set @StartJobId = @OldJobID
declare csr1 cursor for
select j.job_id , js.step_id, js.step_name
from msdb..sysjobs j
inner join msdb..sysjobsteps js
on j.job_id = js.job_id
where j.[name] like 'ALZ DB Maintenance%'
order by j.[name], js.step_id
open csr1
FETCH NEXT FROM csr1
INTO @Job_Id, @step_id, @step_name
WHILE @@FETCH_STATUS = 0
BEGIN
if @OldJobID = @Job_Id
begin
set @NewComment = @NewComment + ',' + @step_name
end
else
begin
if @OldJobID = @StartJobId
begin
-- first passthrough
print 'started'
end
else
begin
print @OldJobID
print '---> ' + @NewComment
end
select @OldJobID = @Job_Id , @NewComment = @step_name
end
-- Volgende rij inlezen
FETCH NEXT FROM csr1
INTO @Job_Id, @step_id, @step_name
END
-- last items
print @OldJobID
print '---> ' + @NewComment
-- Cursor afsluiten
CLOSE csr1
DEALLOCATE csr1
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 23, 2006 at 6:14 am
I create one procedure For This.
Anyway thanks for you useful feedback.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply