?
Solved

Pivot or Cross Tab in SQL 2008

Posted on 2014-02-27
4
Medium Priority
?
426 Views
Last Modified: 2014-03-04
Hello Experts;

I don’t know if I need pivot or cross tabs.  A client wants their report to look like the attachment.

The data is stored in several tables with the User and Course Started stored in joining to another table with Course Number and Course Name and another joining to the Area and Office.   The pivot or cross tab is with the Questions.  These results are stored in another table that contains the User’s id with the QuestionID, AnswerID, TextAnswer and NumbericAnswer in SQL row column format.

If I did not give enough information just yell.
pivot.docx
0
Comment
Question by:wdbates
[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
  • 3
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39893607
it appears to be a pivot as the questions have become columns...

If you need more than this as an answer it would help if:
a. you provide the table structures, and
b. you provide sample data

The document attached would serve as the "expected result"
0
 

Author Comment

by:wdbates
ID: 39895307
Thank you for your quick response.  I have attached the query, the query results and expected results.
Query.docx
QueryResults.docx
ExpectedResults.xlsx
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 900 total points
ID: 39901938
The maximum length of any column heading is 128. Can you live with that restriction?

btw: In your example Item005 is a total of 71 chars.
Item005_Please provide your thoughts about any aspect of this training.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39904921
I guess you must have produced the pivot query and no longer need assistance with it.

My last comment here was a question intended to ensure that a query would meet your needs. Please also note that whilst you were kind enough to provide a query result it is way better to provide raw data from each table - that way we can actually build test tables, with data, and then produce a working query.

One last observation. It isn't necessary to use a document for such things as the existing query, instead a "code block" is preferred:
Select UserId = CASE u.StudentID 
					WHEN NULL THEN 
						CASE u.ntlogon
						WHEN NULL then u.ExternalID
					ELSE NTLogon
					END
				ELSE u.studentid 
				END,  
       e1.ExtAreaValue	as "Area",
       e1.ExtROValue	as "Office",
       e1.surveyid	as "SurveyID",      
       e1.surveydate	as "SurveyDate",
       sq.ExternalQID	as "QuestionID" , 
       q.question		as "Question",
       Answer = CASE q.QuestTypeID 
                        WHEN 5 then  CASE 
                           WHEN r.TextAnswer IS NULL THEN ''
                           ELSE replace (replace (r.TextAnswer, CHAR(10), ' '), CHAR(13), ' ')
                          END 
                        ELSE  CASE 
                           WHEN a.Answer IS NULL THEN ''
                           ELSE a.Answer 
                          END 
                       END
from SurveyQuest sq 
inner join SurQuestion q on q.questionid = sq.QuestionID 
cross join SurRespondent e1 
inner join userdata u on u.UserID = e1.UserID 
left join SurResponse r on r.RespondentID = e1.RespondentID and r.QuestionID = sq.QuestionID 
Left join SurAnswer a on a.AnswerID = r.AnswerID
inner join SurRefData sfd on sfd.surveyid = e1.surveyid and sfd.LTID = e1.LTID and sfd.TrainingMode = e1.TrainingMode
where e1.surveyid = sq.surveyid
order by e1.respondentid,  sq.SurveyID, sq.QuestionID

Open in new window

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

649 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