I have a SP that returns the information I want but it returns it in 2
separate queries.
Example:
Query 1
Name, Number, Class
Row 1- Mike Phillips, 154AA, AA
and
Query 2
Time, Manual
Row 1 -12:45:22,0
Row 2 -13:04:56,0
What I want it to look like is:
Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0
Here is the query I'm using:
DECLARE Class cursor
FOR
--here we get a list of distinct classes to pass to the Class cursor
select Distinct(class_ID) from kt_member_lap
where Race_ID = 83
order by Class_ID;
OPEN Class;
DECLARE @RaceID int
DECLARE @RacerCount int
DECLARE @ClassID char(50)
DECLARE @classcount
DECLARE @Racer char(50)
DECLARE @i int
SET @RaceID = 83
--this is where we loop through the classes
FETCH NEXT FROM Class INTO @ClassID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
DECLARE Lap cursor
FOR
Select DISTINCT(Member_ID) from KT_MEMBER_LAP
Where class_ID = @classID and race_id = @RaceID
OPEN Lap;
--this is to begin counting from the first lap
SET @i = 1;
FETCH NEXT FROM Lap INTO @Racer
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SELECT KT_MEMBER.MEMBER_FNAME + ' ' +
KT_MEMBER.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3),
KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER As
BikeNumber,
KT_CLASS.CLASS_DESC
FROM KT_CLASS INNER JOIN
KT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =
KT_MEMBER_CLASS.CLASS_ID INNER JOIN
KT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =
KT_MEMBER.MEMBER_ID
WHERE KT_MEMBER.MEMBER_ID = @Racer and KT_CLASS.CLASS_ID =
@ClassID
--SELECT @Racer, @ClassID
Select MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAP
Where Member_ID = @Racer and class_ID = @classID and race_id =
@RaceID
ORDER BY MEMBER_LAP_TIME_REAL
--here I count up for the next lap
SET @i = @i + 1;
FETCH NEXT FROM Lap INTO @Racer
END
CLOSE Lap;
DEALLOCATE Lap;
FETCH NEXT FROM Class INTO @ClassID
END
CLOSE Class;
DEALLOCATE Class;
Any help would be appreciated.
