Database

// This needs MARS:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
    connection.Open();
    using (IDataReader outerReader = outerCommand.ExecuteReader())
    {
        while (outerReader.Read())
        {
            using (var innerCommand = new SqlCommand("...", connection))
            {
                using (IDataReader innerReader = innerCommand.ExecuteReader())
                {
                    while (innerReader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

// This doesn't:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    connection.Open();
    using (IDataReader reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                ...
            }
        } 
        while (reader.NextResult())
    }
}

// Neither does this:
var dataSet = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    var adapter = new SqlDataAdapter(command);
    adapter.Fill(dataSet);
}

create table tqw1(t1c1 varchar2(10),t1c2 varchar2(10),t1c3 varchar2(10),t1c4 varchar2(10));
create table tqw2(t2c1 varchar2(10),t2c2 varchar2(10),t2c3 varchar2(10),t2c4 varchar2(10));
create table tqw3(t3c1 varchar2(10),t3c2 varchar2(10),t3c3 varchar2(10),t3c4 varchar2(10));
create table tqw4(t4c1 varchar2(10),t4c2 varchar2(10),t4c3 varchar2(10),t4c4 varchar2(10));

insert into tqw1(t1c1,t1c2,t1c3,t1c4) values (7,2,3,4);
insert into tqw2(t2c1,t2c2,t2c3,t2c4) values (8,3,4,5);
insert into tqw3(t3c1,t3c2,t3c3,t3c4) values (9,4,5,6);
insert into tqw4(t4c1,t4c2,t4c3,t4c4) values (10,5,6,7);

select Q.T1C1,W.T2C2,E.T3C3,R.T4C4 from tqw1 q,TQW2 w,TQW3 e, TQW4 r ;

KipkoechE wrote:

i wish to know how i can loop through the records of each student and insert into TotalScore column

UPDATE tablename SET TotalScore = (unit1 + unit2 + unit3 + unit4) // and so on?

KipkoechE wrote:

the number of columns can be changed at runtime

DECLARE @temp NVARCHAR(MAX)
SELECT @temp = COALESCE(@temp + ', ', '') + field1 
FROM table

SELECT
    STUFF(
        (SELECT '/' + Surname + ',' + STUFF(
            (SELECT '-' + Forename 
            FROM YourTable As T2 
            WHERE T2.Surname = T1.Surname 
            ORDER BY T2.Forename 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        , 1, 1, '')
        FROM YourTable As T1
        GROUP BY Surname
        ORDER BY Surname
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    , 1, 1, '') As CombinedNames
;