?
Solved

MS SQL PIVOT

Posted on 2011-09-26
7
Medium Priority
?
264 Views
Last Modified: 2012-08-13
ive dsigned DB for a feedback form

the table where i've stored the feedback is like this:

empid        q1              q2            
123            1                2              

the value in column q1 & q2 is the option that is selected


master table for questions & answers:

questionnumber                  optionnumber                      optionvalue
           1                                       1                                          yes
           1                                       2                                          no
           2                                       1                                          yes
           2                                       2                                            no
           2                                       3                                           maybe



now, i wish to display what options each user has selected ... basically, transform the column in row?

the report on the front-end has the employee id on top, then one below the other all questions & all options... and the option which he has selected will be ticked marked

please guide me on the DB part of this operation
0
Comment
Question by:the_7th_king
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 39

Expert Comment

by:appari
ID: 36707806
try this
SELECT empID, QuestionNo, response
FROM 
   (SELECT empID, q1, q2
   FROM @pvt) p
UNPIVOT
   (response FOR QuestionNo IN 
      (q1, q2)
)AS unpvt

Open in new window

0
 
LVL 2

Author Comment

by:the_7th_king
ID: 36708035
that works fine... but i need to join those values to the main questions' table so that i will have all the question/answer AND then see the selected one!
0
 
LVL 39

Expert Comment

by:appari
ID: 36708200
try this
;with userAns as 
(
SELECT empID, QuestionNo, response
FROM 
   (SELECT empID, q1, q2
   FROM @pvt) p
UNPIVOT
   (response FOR QuestionNo IN 
      (q1, q2)
)AS unpvt
)
Select 
questions.*, case when userAns.response = optionnumber then 'Selected' else null end 
From questions Left Outer Join userAns
on userAns.QuestionNo = questions.questionnumber 

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Expert Comment

by:AlokJain0412
ID: 36708292


 in following example Put your table name inplace of aa
 
SELECT empID, QuestionNo, response,case when response = 1 then 'Yes' when response=2 then 'No' else 'Maybe' End As responseValue
FROM
   (SELECT empID, q1, q2
   FROM aa) p
UNPIVOT
   (response FOR QuestionNo IN
      (q1, q2)
)AS unpvt
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36709188
your basic problem is the design of the feedback table

instead of a single row you should be storing a row per question response...

then if you want to display as a single row that becomes a simple pivot operation...

empid       questionnumber questionresponse            

the table(or other associated tables) should also have other columns so that the date of the feedback and the feedback type can be identified etc...    
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 36709236
try this with your existing structure
select y.empid, [1] as Q1,[2] as Q2
from (
Select x.empid ,x.quest,m.optionvalue
from (
SELECT empID, convert(int,stuff(QuestionNo,1,1,'')) as quest, response
FROM 
   (SELECT empID, q1, q2
   FROM feedbacktable) p
UNPIVOT
   (response FOR QuestionNo IN 
      (q1, q2)
)AS unpvt
) as x
left outer join mastertable as m
on x.quest=m.questionnumber
and x.response=m.optionnumber
) as y
pivot (max(optionvalue) for quest in ([1],[2])) as pvt
order by 1

Open in new window

0
 
LVL 2

Author Closing Comment

by:the_7th_king
ID: 36813704
thanks!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question