Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

Turn multiple columns into rows

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 * 
From (
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

Open in new window

Help.xls
0
brad2575
Asked:
brad2575
  • 2
1 Solution
 
spprivateCommented:
Here is a link which helped me do this.Basically you need to create a stored proc
and pass the parameters

http://p2p.wrox.com/sql-server-2000/24916-pivot-table.html
0
 
brad2575Author Commented:
This looks like it would work for a single table and a single value only.  I could alter it to work with my Join statement above but not sure about the multiple fields?

0
 
brad2575Author Commented:
Gave you a C only because it was not what I was looking for but you still tried to help out.  I went with a SP and a temp tables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now