Solved

Pivot or Cross Tab in SQL 2008

Posted on 2014-02-27
4
404 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
  • 3
4 Comments
 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 300 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 48

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now