Convert Record Set to View - SQL Server 2005

[stoneheart]

I'm hoping someone can point me to the right direction for this. I want to be able to convert my existing query which returns multiple records into a view that would look sort of like a spreadsheet.

My query that returns something like this:

1 Number_of_Bolts 11
2 Number_of_Nails 325
3 Number_of_Pins 1355

The query itself is:

select question_id, question_name, question_value
from question_data d
inner join question_dictionary i
on d.question_id = i.question_id
where master_id = '2111'

Given this, how do I get it into a format where all the records are on the same row spreadsheet style and the key identifier like Number_of_Bolts is a cell header with 11 as the value underneath it? I want this be a view in SQL Server.

Thanks for any hints you all can provide.

[Hack]

Welcome to DevX

Hope these help...

http://technet.microsoft.com/en-us/l.../ms187956.aspx

http://www.codeproject.com/KB/database/sqlviews.aspx

[glafmitzpery]

not sure if u need help on creating the view or how to get the data into a single row. if it's the latter:

sql server 2005 introduced the PIVOT operator. the unfortunate part is that with "pivot" you lose the "dynamic" aspects of a question table. you need to know in advance the "question_name" values and hard-code them into the pivot.

here is your initial data set:

select 'Number_of_Bolts' Question_Name, 11 Question_Value
union
select 'Number_of_Nails', 325
union
select 'Number_of_Pins', 1355

here is the pivot query (replace the inner SQL "PivotSource" with your SQL that gets the list of questions):

SELECT [Number_of_Bolts]
, [Number_of_Nails]
, [Number_of_Pins]
FROM (select 'Number_of_Bolts' Question_Name, 11 Question_Value
union
select 'Number_of_Nails', 325
union
select 'Number_of_Pins', 1355) As PivotSource
PIVOT
(Min (Question_Value) for Question_Name in ([Number_of_Bolts], [Number_of_Nails], [Number_of_Pins]) ) As p1;

Resultset:

Number_of_Bolts Number_of_Nails Number_of_Pins
--------------- --------------- --------------
11 325 1355

(1 row(s) affected)