Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL PIVOT

Posted on 2011-09-26
7
Medium Priority
?
274 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 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