Solved

MS SQL PIVOT

Posted on 2011-09-26
7
254 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 35
Need help with T-SQL on SQL Server 2014 9 37
SQL Script to Remove Data from Two Joined Tables 1 19
Location of Dynamics AX Service accounts in SQL 3 15
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

807 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