insert by rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert by rows

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 03:09:33

I have 4 tables. Student,Mark,SubjectGroup,Subjects. My problem is, I
am storing student mark in marks table. The only relation between these two tables
are schoolid and registernumber

In students table we have subject group which is related to Subjectgroup table.

Subject & subject Group table are related by SubjectGroupID

My problem is, I have to create a temporary table having values of regno,schoolid,subjectname,mark

It should look like

Schoolid regno Mark MinMark MaxMark

ab0 111 12 21 50
ab0 111 26 21 50
ab0 222 43 21 50
ab0 222 44 21 50
ab0 333 22 22 50
ab0 333 24 22 50
ab0 444 34 21 50
ab0 444 35 21 50

That means Marks for each student should be stored row by row insted of column by column

Thank you Ceema

Student
-------

RegNo SchoolID SubjectGroupID

111 ab0 A
222 ab0 A
333 ab0 B
444 ab0 A

SubjectGroup
------------

SubjectGroupID SchoolID SubjectCode MinMark MaxMark

A ab0 1 21 50
A ab0 2 21 50
B ab0 1 22 50
B ab0 2 22 50

Subject
-------

SchoolID SubjectGroupID SubjectCode SubjectName

ab0 A 1 English
ab0 A 2 French
ab0 B 1 Maths
ab0 B 2 French

Mark

SchoolID RegNo Mark01 Mark02

ab0 111 12 26
ab0 222 43 44
ab0 333 22 24
ab0 444 34 35

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 06:35:16

Before you do anything, you should normalise your Mark table - it will make this problem much easier, and will prevent many a headache in the future.

That said, this gives the result you require for the question you stated...

--data
declare @Student table (RegNo int, SchoolID varchar(10), SubjectGroupID varchar(10))
insert @Student
          select 111, 'ab0', 'A'
union all select 222, 'ab0', 'A'
union all select 333, 'ab0', 'B'
union all select 444, 'ab0', 'A'

declare @SubjectGroup table (SubjectGroupID varchar(10), SchoolID varchar(10), SubjectCode int, MinMark int, MaxMark int)
insert @SubjectGroup
          select 'A', 'ab0', 1, 21, 50
union all select 'A', 'ab0', 2, 21, 50
union all select 'B', 'ab0', 1, 22, 50
union all select 'B', 'ab0', 2, 22, 50

declare @Subject table (SchoolID varchar(10), SubjectGroupID varchar(10), SubjectCode int, SubjectName varchar(10))
insert @Subject
          select 'ab0', 'A', 1, 'English'
union all select 'ab0', 'A', 2, 'French'
union all select 'ab0', 'B', 1, 'Maths'
union all select 'ab0', 'B', 2, 'French'

declare @Mark table (SchoolID varchar(10), RegNo int, Mark01 int, Mark02 int)
insert @Mark
          select 'ab0', 111, 12, 26
union all select 'ab0', 222, 43, 44
union all select 'ab0', 333, 22, 24
union all select 'ab0', 444, 34, 35

--calculation
select s.Schoolid, s.regno, m.Mark01 as Mark, g.MinMark, g.MaxMark
from @Student s
    inner join @Mark m on s.SchoolID = m.SchoolID and s.RegNo = m.RegNo
    inner join @SubjectGroup g on s.SchoolID = g.SchoolID and s.SubjectGroupID = g.SubjectGroupID
where SubjectCode = 1

union all

select s.Schoolid, s.regno, m.Mark02, g.MinMark, g.MaxMark
from @Student s
    inner join @Mark m on s.SchoolID = m.SchoolID and s.RegNo = m.RegNo
    inner join @SubjectGroup g on s.SchoolID = g.SchoolID and s.SubjectGroupID = g.SubjectGroupID
where SubjectCode = 2

order by s.Schoolid, s.regno, Mark

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 07:34:43

Hello Ryan Randall,

Thank you for the post, it helped me a lot. Could you please do one more favour, is there a way to display the subjectname also in the same result?

I can't change my table structure, sincce my table is created by our old developers and we are making a new version in front end with the same table.

Thank you so much

Ceema

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 08:03:38

Just let the Subject table join in...

--data
declare @Student table (RegNo int, SchoolID varchar(10), SubjectGroupID varchar(10))
insert @Student
          select 111, 'ab0', 'A'
union all select 222, 'ab0', 'A'
union all select 333, 'ab0', 'B'
union all select 444, 'ab0', 'A'

declare @SubjectGroup table (SubjectGroupID varchar(10), SchoolID varchar(10), SubjectCode int, MinMark int, MaxMark int)
insert @SubjectGroup
          select 'A', 'ab0', 1, 21, 50
union all select 'A', 'ab0', 2, 21, 50
union all select 'B', 'ab0', 1, 22, 50
union all select 'B', 'ab0', 2, 22, 50

declare @Subject table (SchoolID varchar(10), SubjectGroupID varchar(10), SubjectCode int, SubjectName varchar(10))
insert @Subject
          select 'ab0', 'A', 1, 'English'
union all select 'ab0', 'A', 2, 'French'
union all select 'ab0', 'B', 1, 'Maths'
union all select 'ab0', 'B', 2, 'French'

declare @Mark table (SchoolID varchar(10), RegNo int, Mark01 int, Mark02 int)
insert @Mark
          select 'ab0', 111, 12, 26
union all select 'ab0', 222, 43, 44
union all select 'ab0', 333, 22, 24
union all select 'ab0', 444, 34, 35

--calculation
select s.Schoolid, s.regno, m.Mark01 as Mark, g.MinMark, g.MaxMark, j.SubjectName
from @Student s
    inner join @Mark m on s.SchoolID = m.SchoolID and s.RegNo = m.RegNo
    inner join @SubjectGroup g on s.SchoolID = g.SchoolID and s.SubjectGroupID = g.SubjectGroupID
    inner join @Subject j on g.SchoolID = j.SchoolID and g.SubjectGroupID = j.SubjectGroupID and g.SubjectCode = j.SubjectCode
where g.SubjectCode = 1

union all

select s.Schoolid, s.regno, m.Mark02, g.MinMark, g.MaxMark, j.SubjectName
from @Student s
    inner join @Mark m on s.SchoolID = m.SchoolID and s.RegNo = m.RegNo
    inner join @SubjectGroup g on s.SchoolID = g.SchoolID and s.SubjectGroupID = g.SubjectGroupID
    inner join @Subject j on g.SchoolID = j.SchoolID and g.SubjectGroupID = j.SubjectGroupID and g.SubjectCode = j.SubjectCode
where g.SubjectCode = 2

order by s.Schoolid, s.regno, Mark

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 08:14:19

Actually, it's probably better to normalise as soon as possible and then do the query based on that.

Use this calculation instead.

select s.Schoolid, s.regno, m.Mark, g.MinMark, g.MaxMark, j.SubjectName
from @Student s
    inner join (
        select SchoolID, RegNo, 1 as SubjectCode, Mark01 as Mark from @Mark union all
        select SchoolID, RegNo, 2,                Mark02         from @Mark) m --NormalisedMark
        on s.SchoolID = m.SchoolID and s.RegNo = m.RegNo
    inner join @SubjectGroup g on s.SchoolID = g.SchoolID and s.SubjectGroupID = g.SubjectGroupID and g.SubjectCode = m.SubjectCode 
    inner join @Subject j on g.SchoolID = j.SchoolID and g.SubjectGroupID = j.SubjectGroupID and g.SubjectCode = j.SubjectCode
order by s.Schoolid, s.regno, Mark

Better yet, create a view called 'NormalisedMark' (however you want to spell it!), define it the same as the derived table above, and then use that in your query. It's so much easier working with normalised data

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-17 : 09:16:51

RyanRandall,

This query is having extra fields in joining, like

g.SubjectCode=m.SubjectCode,g.SubjectGroupID=j.SubjectGroupID etc.

So, it's not giginf the exact result as expected. I tried to avoid those and it's giging the nswer two times. please help

Thanks
Ceema

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 09:37:39

Hi Ceema,

Try to find and post some of the data that's giving you problems. It's hard for me (or anyone else) to help unless I can see the problem you're getting with an example.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources