Converting Rows into Columns MS SQL 2K

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.[posted and mailed, please reply in news]

dare197 (daniel.white@.perceptivetech.com) writes:
> 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

Could there be any number of Time, Manual rows or is there never more
than two? I will assume that you always have two. Then you can try
this query:

SELECT m.MEMBER_FNAME + ' ' + m.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3), mc.MEMBER_CLASS_BIKE_NUM) +
c.CLASS_LETTER As BikeNumber,
c.CLASS_DESC,
ml1.MEMBER_LAP_TIME_REAL AS "Time 1",
ml1.member_lap_manual AS "Manual 1",
ml2.MEMBER_LAP_TIME_REAL AS "Time 2",
ml2.member_lap_manual AS "Manual 2",
FROM KT_CLASS c
JOIN KT_MEMBER_CLASS mc ON c.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER m ON mc.MEMBER_ID = m.MEMBER_ID
JOIN KT_MEMBER_LAP ml1 ON ml1.MEMBER_ID = m.MEMBER_ID
AND mll.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER_LAP ml2 ON ml2.MEMBER_ID = m.MEMBER_ID
AND ml2.CLASS_ID = mc.CLASS_ID
AND ml2.member_lap_manual > ml1.member_lap_manual
WHERE m.MEMBER_ID = @.Racer
AND c.CLASS_ID = @.ClassID
ORDER BY c.CLASS_ID, m.MEMBER_ID

Here I have collapsed everything into one query, without any cursor, as
I could see no need for a cursor. Cursors can be a magnitude slower than
set-based statements, so there all reasons to avoid them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp