What the best way to get values in multiple rows and set variables with out cursors in SQL Server? (PIVOT more or less)

Question

I could not think of a better question for the problem, but here it is. I have 3 tables in a many to many relationship like:

Students -> StudentTasks <- Tasks

The student task has a column called "Marked", and when a student is created a create a record for possible tasks and Marked = 0, Ex:

Student ---------

Given 
  "StudentA -> Id = 1"

Tasks ------------

Given 
  "Task A -> Id = 1"
  "Task B -> Id = 2"


StudentTasks ------

StudentId -- TaskId -- Marked

1         --   1    --    0

1         --   2    --    0

What I need is for each row in student task I have to set variable @TaskA, @TaskB with the respective "Marked" value, so I can update another table's column with it.

Another way to solve the problem would be to update the table directly, so given the same scenario, but with the addition of a table like so:

StudentId -- TaskA -- TaskB

I would like to see it filled like this:

StudentId -- TaskA -- TaskB

1         --   0   --   0

and if we had "StudentB -< Id = 2" with TaskB marked a 1 we would have:

StudentId -- TaskA -- TaskB

1         --   0   --   0

2         --   0   --   1

The way I am doing is not efficient at all, its taking 40 seconds to go through 3300 records (I am using cursors to walk through the list of students), any suggestions are welcome.

UPDATES:

Using the idea @djangojazz did with the self extracting queries

DECLARE @Student TABLE ( StudentID INT IDENTITY, Name VARCHAR(50));

INSERT INTO @Student VALUES ('Brett'),('Sean')

DECLARE @StudentTasks TABLE (StudentID INT, TaskId INT, Marked BIT);

INSERT INTO @StudentTasks VALUES (1,1,0),(1,2,0),(2,1,0),(2,2,1)

DECLARE @Tasks TABLE (TaskID INT IDENTITY, NAME VARCHAR(50));

INSERT INTO @Tasks VALUES ('Study'),('Do Test')


SELECT * FROM @Student
SELECT * FROM @StudentTasks
SELECT * FROM @Tasks

-- THIS IS WHAT I NEED IN THE RESULT

DECLARE @ResultTable TABLE (StudentId INT, Study BIT, DoTest BIT);

INSERT INTO @ResultTable VALUES (1,0,0),(2,0,1)

SELECT * FROM @ResultTable

Answer 1

UPDATED 6-13-13. I don't even think you need the 'Students' table right off the bat as you just want to pivot on the identifier. The problem may become though if you use repeat values for anything in the future this logic will break. Meaning if you have a table where a Student Id may be repeated for another value with a type other than bit you would then need to perform more operations to see which was the most current by an identity or such. Saying that though I can give you what you say you wanted.

DECLARE @Student TABLE ( StudentID INT IDENTITY, Name VARCHAR(50));

INSERT INTO @Student VALUES ('Brett'),('Sean')

DECLARE @StudentTasks TABLE (StudentID INT, TaskId INT, Marked BIT);

INSERT INTO @StudentTasks VALUES (1,1,0),(1,2,0),(2,1,0),(2,2,1)

DECLARE @Tasks TABLE (TaskID INT IDENTITY, NAME VARCHAR(50));

INSERT INTO @Tasks VALUES ('Study'),('Do Test')

DECLARE @ResultTable TABLE (StudentId INT, Study BIT, DoTest BIT);

INSERT INTO @ResultTable VALUES (1,0,0),(2,0,1)

select 'Results you wanted'
SELECT * 
FROM @ResultTable

select 'Method A'
;


-- with case when forcing a pivot on an expression
With x as 
    (
    select
        st.StudentID
    ,   cast(st.Marked as tinyint) as Marked
    ,   t.NAME
    from @StudentTasks st 
        join @Tasks t on st.TaskId = t.TaskID
    )
Select
    x.StudentID
,   max(case when NAME = 'Study' then Marked end) as Study
,   max(case when NAME = 'Do Test' then Marked end) as DoTest
FROM x
group by x.StudentID

Select 'Method B'
;

-- with traditional pivot you need to translate names I believe
With x as 
    (
    select
        st.StudentID
    ,   cast(st.Marked as tinyint) as Marked
    ,   case when t.NAME = 'Study' then 0 else 1 end as Name
    from @StudentTasks st 
        join @Tasks t on st.TaskId = t.TaskID
    )
Select
    pvt.StudentID
,   [0] as Study
,   [1] as 'Do Test'
FROM x
    pivot(max(x.Marked) for x.Name in ([0], [1])) as pvt

Answer 2

PIVOT the StudentTasks table for the 2 Tasks - A and B, JOIN it with the 'Additional table' on StudentID and Update it ..