SQL Query - Pivot output so columns become rows

[jondow]

Hi, I have the following SQL as part of a SP (SQL Server 2000):

DECLARE @queid int
SET @queid = 27
DECLARE @camid int
SET @camid = 1
DECLARE @UserID UniqueIdentifier
SET @UserID = 'abcdef'

SELECT
dbo.tblAnswerResponse.ars_text, dbo.tblResponse.res_id, dbo.tblQuestionAnswerType.qat_prefix
FROM
dbo.tblQuestionAnswerType
INNER JOIN dbo.tblResponse ON dbo.tblQuestionAnswerType.qat_id = dbo.tblResponse.qat_id
INNER JOIN dbo.tblAnswerResponse ON dbo.tblResponse.res_id = dbo.tblAnswerResponse.res_id
WHERE
(dbo.tblQuestionAnswerType.que_id = @queid)
AND (dbo.tblResponse.UserID = @UserID)
AND (dbo.tblResponse.cam_id = @camid)
ORDER BY
dbo.tblResponse.res_id


Which gives an output in the following format:
text id prefix
school 151 Name
london 152 Location
filton 151 Name
bristol 152 Location

My problem is I want to pivot the output so that the prefix columns become column headers and the text sits under the correct column as follows

id Name Location
151 school london
152 filton bristol

I have seen a number of ways to complete this when there are a fixed number of columns, however I could have 1.Name, 2.Location, 3.Another, n.... coulmns

Does anyone have any solutions for this?

Thanks!

 

[SQLSister]

NO simple way in SQl Server 2000 (one reason to upgrade to 2005)

but these FAQs should get you started

http://www.tek-tips.com/faqs.cfm?fid=5269

http://www.tek-tips.com/faqs.cfm?fid=5278

"NOTHING is more important in a database than integrity." ESquared