• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
0
the_7th_king
Asked:
the_7th_king
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
LowfatspreadCommented:
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
 
the_7th_kingAuthor Commented:
thanks!
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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