Stored Proc Help - 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 2008 Forums

Transact-SQL (2008)

Stored Proc Help

Author

Topic

spdeveloper
Starting Member

1 Post

Posted - 2011-06-19 : 21:13:58

The following is my database diagram. A student can have any number of schools and any number of teams.

When I write the following stored proc, I get all the school names and team names for each student.
Select RowNo,
SchoolName,
TeamName,
FKStudentID,
ROW_NUMBER() Over(Order by FKStudentID) as RowNo

From Student_School
Left
Outer
Join School
On StudentSchoolID = FKStudentSchoolID
Left
Outer
Join Team
On FKStudentTeamID = TeamID
Where SchoolName Is Not Null
Or TeamName Is Not Null

1) How do I take the first two school names and team names for each student and get a new table? (If the above stored proc returns more than two school names or team names for a student, I dont want all of them in the new table. I want only the first two for each student).
2) Then in the new table, I want to combine the two rows for each student into a single row with the following columns
StudentID, SchoolName1,TeamName1, SchoolName2 and TeamName2.

All I am trying to achieve is the last step. Please let me know of a possible solution (or an approach).

Thanks.

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-22 : 04:27:47

USE [sqlteam_learn]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---ddl definition
drop table [dbo].[STUDENT_SCHOOL];
CREATE TABLE [dbo].[STUDENT_SCHOOL](
[RowNo] [int] NOT NULL,
[FKStudentID] [int] NOT NULL,
[FKStudentSchoolID] [int] NOT NULL,
[FKStudentTeamID] [int] NOT NULL,
) ON [PRIMARY]

GO

drop table [dbo].[SCHOOL];
CREATE TABLE [dbo].[SCHOOL](
[StudentSchoolID] [int] NOT NULL,
[SchoolName] nvarchar(20) NOT NULL,
) ON [PRIMARY]

GO

drop table [dbo].[Team];
CREATE TABLE [dbo].[Team](
[TeamID] [int] NOT NULL,
[TeamName] nvarchar(20) NOT NULL,
) ON [PRIMARY]

GO

DROP TABLE Final_Student_School_Team;
create table Final_Student_School_Team
( [RowNo] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SchoolName1] nvarchar(20) NOT NULL,
[TeamName1] nvarchar(20) NOT NULL,
[SchoolName2] nvarchar(20) NOT NULL,
[TeamName2] nvarchar(20) NOT NULL,
) ON [PRIMARY];

GO

--DATA PREPARETION
insert into Team values(1,'Team No.1');
insert into Team values(2,'Team No.2');
insert into Team values(3,'Team No.3');
insert into Team values(4,'Team No.4');
insert into Team values(5,'Team No.5');

insert into SCHOOL values(1,'School No.1');
insert into SCHOOL values(2,'School No.2');
insert into SCHOOL values(3,'School No.3');
insert into SCHOOL values(4,'School No.4');
insert into SCHOOL values(5,'School No.5');

insert into [STUDENT_SCHOOL] values(1,1,1,1);
insert into [STUDENT_SCHOOL] values(2,1,1,2);
insert into [STUDENT_SCHOOL] values(3,1,1,3);
insert into [STUDENT_SCHOOL] values(4,1,2,1);
insert into [STUDENT_SCHOOL] values(5,2,1,1);
insert into [STUDENT_SCHOOL] values(6,2,2,1);
insert into [STUDENT_SCHOOL] values(7,2,3,1);

---Method 1
insert into Final_Student_School_Team
SELECT Row_Number()Over(order by Row1.FKStudentID ) RowNo, Row1.FKStudentID as StudentID
,Row1.SchoolName as SchoolName1,Row1.TeamName as TeamName1
,Row2.SchoolName as SchoolName2,Row1.TeamName as TeamName2
FROM
(
select *
from
(
select ROW_NUMBER() over(partition by ss.FKStudentID order by ss.FKStudentID )as RowNo
,ss.FKStudentID,s.SchoolName,t.TeamName
from STUDENT_SCHOOL ss
left outer join
SCHOOL s
on ss.FKStudentSchoolID=s.StudentSchoolID
left outer join
Team t
on ss.FKStudentTeamID=t.TeamID
where s.StudentSchoolID IS NOT NULL
and t.TeamName IS NOT NULL
)tmp1
where RowNo=1
)Row1
LEFT OUTER JOIN
(
select *
from
(
select ROW_NUMBER() over(partition by ss.FKStudentID order by ss.FKStudentID )as RowNo
,ss.FKStudentID,s.SchoolName,t.TeamName
from STUDENT_SCHOOL ss
left outer join
SCHOOL s
on ss.FKStudentSchoolID=s.StudentSchoolID
left outer join
Team t
on ss.FKStudentTeamID=t.TeamID
where s.StudentSchoolID IS NOT NULL
and t.TeamName IS NOT NULL
)tmp2
where RowNo=2
)Row2
on Row1.FKStudentID=Row2.FKStudentID

super_lucky_dog

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-22 : 04:28:22

--Check Result
select *
from Final_Student_School_Team

---Method 2
CREATE TABLE #Result
( [RowNo] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[SchoolName1] nvarchar(20) ,
[TeamName1] nvarchar(20) ,
[SchoolName2] nvarchar(20) ,
[TeamName2] nvarchar(20) ,
) ON [PRIMARY];

TRUNCATE TABLE #Result;
INSERT INTO #Result ([RowNo],[StudentID])
SELECT ROW_NUMBER() OVER(ORDER BY tmp.FKStudentID) RowNo,tmp.FKStudentID
FROM
(
SELECT DISTINCT SS.FKStudentID
FROM sqlteam_learn.dbo.STUDENT_SCHOOL SS
)tmp

DECLARE curColumns INSENSITIVE CURSOR
FOR SELECT RowNo,FKStudentID,SchoolName,TeamName
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY ss.FKStudentID ORDER BY ss.FKStudentID )AS RowNo
,ss.FKStudentID,s.SchoolName,t.TeamName
FROM STUDENT_SCHOOL ss
LEFT OUTER JOIN
SCHOOL s
on ss.FKStudentSchoolID=s.StudentSchoolID
LEFT OUTER JOIN
Team t
on ss.FKStudentTeamID=t.TeamID
where s.StudentSchoolID IS NOT NULL
and t.TeamName IS NOT NULL
)tmp1
where RowNo<=2

DECLARE @RowNo NVARCHAR(1000);
DECLARE @StudentID NVARCHAR(20);
DECLARE @SchoolName NVARCHAR(20);
DECLARE @TeamName NVARCHAR(20);
OPEN curColumns
FETCH NEXT FROM curColumns INTO @RowNo,@StudentID,@SchoolName,@TeamName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Result
SET [SchoolName1]=(CASE @RowNo
WHEN 1 THEN @SchoolName
ELSE [SchoolName1]
END)
,[TeamName1]=(CASE @RowNo
WHEN 1 THEN @TeamName
ELSE [TeamName1]
END)
,[SchoolName2]=(CASE @RowNo
WHEN 2 THEN @SchoolName
ELSE [SchoolName2]
END)
,[TeamName2]=(CASE @RowNo
WHEN 2 THEN @TeamName
ELSE [TeamName2]
END)
WHERE [StudentID]=@StudentID

FETCH NEXT FROM curColumns INTO @RowNo,@StudentID,@SchoolName,@TeamName

END
CLOSE curColumns
DEALLOCATE curColumns

---Check Result
SELECT *
FROM #Result;

super_lucky_dog

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-22 : 04:33:54

hi spdeveloper,
Here are my answers to your question. Hope it helps.
But I wonder the diagram you posted is a simplified one cause there is on relationship bewteen team and school.
This quesion is just a transformation matrices. I think you can find many metholds on web.

Thanks,
v-jinxli

super_lucky_dog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources