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

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
0
wdbates
Asked:
wdbates
  • 3
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now