there's no need for a cursor on this, but I think youa re right, you will need to pivot the rows to columns;
is there a fixed number of answers for each question, or does it vary?
is there a maximum number of answers for each question, ie 4 for a multipel choice question?
here's an example i adapted, since i didn't want to guess at your DDL for your tables.
note that if you can provide the data in the same format int he future, you can get exact, tested answers to your problems:
CREATE TABLE Question (QuestionID INT , QuestionName VARCHAR(255))
INSERT INTO Question values (1,'First Question')
INSERT INTO Question values (2,'Second Question')
go
CREATE TABLE Question_items (QuestionID INT , ItemID INT )
INSERT INTO Question_items values (1, 10)
INSERT INTO Question_items values (1, 20)
INSERT INTO Question_items values (1, 30)
INSERT INTO Question_items values (1, 40)
INSERT INTO Question_items values (2, 50)
INSERT INTO Question_items values (2, 60)
INSERT INTO Question_items values (2, 70)
INSERT INTO Question_items values (2, 80)
go
;with cte as
(
select QuestionID ,ItemID,
row_number() over (partition by QuestionID order by ItemID desc) RN
from Question_items
)
--select * from
select QuestionID,[1] as Val1,[2] as Val2,[3] as Val3,[4] as Val4 from
( select * from cte where RN <= 4 ) pivot_handle
pivot
(MAX(ItemID) for RN in ([1],[2],[3],[4])) pivot_table
Lowell