Solved

Pivot or Cross Tab in SQL 2008

Posted on 2014-02-27
4
416 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 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 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 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