MS SQL PIVOT

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
LVL 2
the_7th_kingAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
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
 
appariCommented:
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
 
the_7th_kingAuthor Commented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
appariCommented:
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
 
AlokJain0412Commented:


 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
 
LowfatspreadCommented:
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
 
the_7th_kingAuthor Commented:
thanks!
0
All Courses

From novice to tech pro — start learning today.