Pivot or Cross Tab in SQL 2008

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
wdbatesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
wdbatesAuthor Commented:
Thank you for your quick response.  I have attached the query, the query results and expected results.
Query.docx
QueryResults.docx
ExpectedResults.xlsx
0
 
PortletPaulfreelancerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.