I have a survey website that collects data and the answers to the questions are stored in a table so that each questionID and answer is in its own row in a database table.
What I want to do is kinda complicated and I KNOW I can do it but I am having a brain freeze currently.
I have the query below that returns a question and an answer from a table (the number of questions/answers is dynamic). The query below only returns one persons questions/answers (for info - if I comment out the line "AND ID.ItemID = '160'" it will return ALL the questions/answers).
I uploaded a spreadsheet that has how it is coming from the query and how I want it.
How do I do this? Then once I do it with this one persons answers to these questions how can I get it to work for ALL users (comment out this:AND ID.ItemID = '160')
I can use any sort of code in TSQL and a Stored procedure to do it, create a dynamic query, etc. I looked at Pivot Tables but either do not understand them or it will not work for what I want it to do.
If you need more details/more examples let me know.
This is only for ONE persons answers to their questions,
Select cast(row_number() over (order by QT.DisplayOrder, QT.QuestionID) as varchar(5000)) + '. ' + QT.QuestionText as Question,
Case When PVL.PropertyDescription IS NULL THEN P.PropertyValue ELSE PVL.PropertyDescription END as QuestionAnswer
From Property P
Inner Join ItemData ID ON P.ItemID = ID.ItemID
Inner Join QuestionTable QT ON QT.QuestionID = P.PropertyCode
Left Outer Join PropertyValueLookup PVL ON PVL.PropertyCode = QT.PropertyCode AND
PVL.PropertyValueID = Case When ISNUMERIC(P.PropertyValue) = '1' Then P.PropertyValue Else PVL.PropertyValueID END
Where QT.TypeID = '8'
AND ID.ItemID = '160'
--AND QT.QUestionID = '9'
) as R