One to Many to One...

[mhoyt]

New Federal Guidelines mandate that we allow each of our students to identify themselves as being a member of at least 1 Race, and as many as 5.

We're using a commercial database where the vendor has seen fit to make 1 record per student in the STUDENT table, and multiple records for each student in the RACE table.

My challange is that I have to produce a flat file with one record per student and include five race fields. The first field will have to be something other than the NA value (00), but the second thru fifth fields might be either 00 or one of the five possiblities. Obviously I cannot duplicate a choice.

So I'm trying to figure out how to identify the second thru fifth choices a student might have made. The RACE table has no provision for this, every record is just the StudentID and a Race Code (01, 02, 03, 04, 05).

I hoped to just pull all Race Codes of 01 and put them in the Race1 field of my output file, but the requirement now is that I cannot have 00 in the Race1 field, and then a value in the second race field. So I can't submit something like 00 00 03 00 05. It would have to be 03 05 00 00 00

So I somehow have to pull a code for every student into Race1, then keep track of what I've already pulled, go back and see if the student has another selection, put that in Race2, mark it as used, and go back and see if they have another selection.....

I think I can do this with some fairly ugly combination of temp table and CASE statements, but I was hoping for something more elegant.

TIA Mike

 

[NoCoolHandle]

This might do it/.....
(the real work is done in the function.. all that is needed is to pass in the student id in a select statement and it should build the output as shown..

e.g. the output from the sample tables for [blue]select Name , dbo.getraces(stid) from students[/blue]
looks like
Name
---------------------------
rob 02 04 05 00 00
von 01 03 00 00 00
geo 03 00 00 00 00

Create database crap
go
use crap
go
Create Table Students(StID int identity primary key, name varchar(300))
create table race (RaceID int identity primary key , name varchar(300))
create table studentrace (Stid int , raceid int )
go
insert into students values('rob')
insert into students values('von')
insert into students values('geo')
go
insert into race values ('race1')
insert into race values ('race2')
insert into race values ('race3')
insert into race values ('race4')
insert into race values ('race5')
GO
INSERT INTO studentrace VALUES (1,2)
INSERT INTO studentrace VALUES (1,4)
INSERT INTO studentrace VALUES (1,5)
INSERT INTO STUDENTRACE VALUES (2,1)
INSERT INTO STUDENTRACE VALUES (2,3)
INSERT INTO STUDENTRACE VALUES (3,3)
GO
Create Function GetRaces (@studentid int)
returns varchar(30)
as
begin
declare @lastID int,@cntr int
declare @race int, @allRaces varchar(200)
Select @allRaces = '',@lastid = 0,@cntr =0
while not @lastid is null
	begin
	   Select @lastid = min(raceid) from studentrace where raceid > @lastid and stid = @studentid
	   if not @lastid is null
		begin
			select @allraces =  @allRaces + ' ' + replicate('0',2- len(cast(@lastid as varchar(20)))) +  cast(@lastid as char(2))
			set @cntr = @cntr + 1
		end	    	
	end

Return @allraces +   replicate(' 00',5 -  cast(@cntr as varchar(300)))
end
go
select  Name , dbo.getraces(stid) from students
go
Drop Database crap