Multiple rows instead of multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple rows instead of multiple columns

Author

Topic

stollerd
Starting Member

2 Posts

Posted - 2011-08-15 : 15:15:58

Please feel to merge with another thread if this has been discussed before, I attempted to search but I'm not even sure I'm describing my issue correctly.

Nature of the front end:
Every time an audit is completed, a form is filled out for each CR-CODE. Inside that form, there are multiple questions.

Goal:
I'd like to be able to report all answers to each question in one row (one row for each CR-CODE with multiple columns - one column for each question).

Issue:
Due to the complex nature of the setup of our database, it's not a simple SELECT statement. I believe the data set is extremely normalized and may require a SUB SELECT of some sort and since my knowledge of SQL is minimal, I haven't been able to find a solution.

Below you can find my code, followed by my current result set, which is followed by my desired result set.

CODE:



SELECT
	ci.crcode
	,max(s.datecompleted) AS datecompleted

/*EOT Answer CASE WHEN statement */
	,(CASE WHEN q.questiontypeid = 11 THEN CONVERT(VARCHAR(2000),sa.AnswerIntValue) ELSE
	(CASE WHEN q.questiontypeid = 12 THEN CONVERT(VARCHAR(2000),qo.OptionName) ELSE
	(CASE WHEN q.questiontypeid = 13 THEN CONVERT(VARCHAR(2000),sa.AnswerTextValue) ELSE
	(CASE WHEN q.questiontypeid = 21 THEN CONVERT(VARCHAR(2000),uf.FileName) ELSE
	(CASE WHEN q.questiontypeid = 23 THEN CONVERT(VARCHAR(2000),sa.AnswerFloatValue) ELSE
	(CASE WHEN q.questiontypeid = 25 THEN CONVERT(VARCHAR(2000),sqo.SelectionValue) ELSE null END)END)END)END)END)END) AS EOTAnswer

/*FY Answer CASE WHEN statement */
	,(CASE WHEN q.questiontypeid = 11 THEN CONVERT(VARCHAR(2000),sa.AnswerIntValue) ELSE
	(CASE WHEN q.questiontypeid = 12 THEN CONVERT(VARCHAR(2000),qo.OptionName) ELSE
	(CASE WHEN q.questiontypeid = 13 THEN CONVERT(VARCHAR(2000),sa.AnswerTextValue) ELSE
	(CASE WHEN q.questiontypeid = 21 THEN CONVERT(VARCHAR(2000),uf.FileName) ELSE
	(CASE WHEN q.questiontypeid = 23 THEN CONVERT(VARCHAR(2000),sa.AnswerFloatValue) ELSE
	(CASE WHEN q.questiontypeid = 25 THEN CONVERT(VARCHAR(2000),sqo.SelectionValue) ELSE null END)END)END)END)END)END) AS FYAnswer

FROM onetool..audit a 
INNER JOIN onetool..session s ON
	a.auditid = s.auditid and s.active =1
INNER JOIN cdb..contractoridentifier ci ON
	s.contractorid = ci.contractorid
INNER JOIN onetool..sessionanswer sa ON
	s.sessionid = sa.sessionid
LEFT OUTER JOIN onetool..sessionansweruploadfile uf ON
	sa.sessionanswerid = uf.sessionanswerid
LEFT OUTER JOIN onetool..questionoptions qo ON
	sa.questionoptionsid = qo.questionoptionsid
LEFT OUTER JOIN onetool..sessionansweroptions sao ON
	sa.sessionanswerid = sao.sessionanswerid --and validanswer = 1
LEFT OUTER JOIN onetool..questionoptions sqo ON
	sao.questionoptionsid = sqo.questionoptionsid
	AND sao.selected = 1
INNER JOIN onetool..question q ON
	sa.questionid = q.questionid
INNER JOIN onetool..sessionmicrosection smc ON
	sa.SessionMicroSectionId = smc.SessionMicroSectionId
INNER JOIN onetool..SessionMinorSection smn ON
	smc.SessionMinorSectionId = smn.SessionMinorSectionId
INNER JOIN onetool..SessionMajorMinorSection smm ON
	smn.SessionMinorSectionId = smm.SessionMinorSectionId
INNER JOIN onetool..SessionMajorSection smj ON
	smm.SessionMajorSectionId = smj.SessionMajorSectionId
INNER JOIN onetool..MajorSection mj ON
	smj.MajorSectionId = mj.MajorSectionId
INNER JOIN onetool..MinorSection mn ON
	smn.MinorSectionId = mn.MinorSectionId
INNER JOIN onetool..MicroSection mc ON
	smc.MicroSectionId = mc.MicroSectionId

WHERE 
(a.auditid= 74
AND s.DATECOMPLETED IS NOT NULL
and q.questionname = 'Which EOT Rating Reason Applies?'
and ci.crcode not in ('107 tha')
AND CONVERT(VARCHAR(2000),sqo.SelectionValue) IS NOT NULL) 

OR 

(a.auditid= 74
AND s.DATECOMPLETED IS NOT NULL
and q.questionname like '%fiscal%'
and ci.crcode not in ('107 tha')
AND CONVERT(VARCHAR(2000),sqo.SelectionValue) IS NOT NULL) 

GROUP BY
ci.crcode
	,ci.productcode
	,UPPER(ci.fctyname)  
	,a.auditname
	,s.sessionname
	,mj.MajorSectionName
	,mn.MinorSectionName
	,mc.MicroSectionName
	,sa.sessionanswerid
	,q.questionnumber
	,q.questionname
	,(CASE WHEN q.questiontypeid = 11 THEN CONVERT(VARCHAR(2000),sa.AnswerIntValue) ELSE
	(CASE WHEN q.questiontypeid = 12 THEN CONVERT(VARCHAR(2000),qo.OptionName) ELSE
	(CASE WHEN q.questiontypeid = 13 THEN CONVERT(VARCHAR(2000),sa.AnswerTextValue) ELSE
	(CASE WHEN q.questiontypeid = 21 THEN CONVERT(VARCHAR(2000),uf.FileName) ELSE
	(CASE WHEN q.questiontypeid = 23 THEN CONVERT(VARCHAR(2000),sa.AnswerFloatValue) ELSE
	(CASE WHEN q.questiontypeid = 25 THEN CONVERT(VARCHAR(2000),sqo.SelectionValue) ELSE null END)END)END)END)END)END)