asked
Pivot sql in 2k5
Good day, I have a result set which makes use of a "Script" which has questions and answeres, linked to 1 case ID. When I run this query, I get many duplicates with only the questions and answeres which is different. I am now trying to pivot the TSQL so that the questions will be the column header and the answers will be in the rows. I would need to pivot the query, but there are no aggrigation. I just need to display the data. Any help from anybody would be appreciated. DECLARE @columns NVARCHAR(4000) set @columns = (COALESCE(@columns + '[' + cast(nvchQuestion as nvarchar) + ']', '[' + cast(nvchQuestion as nvarchar)+ ']')) (SELECT DISTINCT VR_CaseDetail.gCaseID, VR_CaseDetail.nvchCaseNo, VR_CaseServiceItem.dtUpdateDate, VR_CaseMember.nvchLastName, VR_CaseMember.nvchFirstName, VR_CaseServiceItem.dAmount, VR_CaseServiceItem.CT_ITEM, VR_CaseClient.nvchCoName, VR_CaseScriptAnswer.nvchQuestion, VR_CaseScriptAnswer.nvchAnswer, VR_case_provider.provider_name, VR_CaseScript.gTemplateID FROM VR_ScriptQuestion INNER JOIN VR_CaseScript ON VR_ScriptQuestion.gTemplateID = VR_CaseScript.gTemplateID INNER JOIN VR_CaseScriptAnswer ON VR_CaseScript.gScriptID = VR_CaseScriptAnswer.gScriptID INNER JOIN VR_CaseMember INNER JOIN VR_CaseInvoiceDetail RIGHT OUTER JOIN VR_CaseClient INNER JOIN VR_CaseAlarmCenter INNER JOIN VR_CaseDetail INNER JOIN VR_CaseService ON VR_CaseDetail.gCaseID = VR_CaseService.gCaseID INNER JOIN VR_CaseServiceItem ON VR_CaseService.gServiceID = VR_CaseServiceItem.gServiceID ON VR_CaseAlarmCenter.gCaseID = VR_CaseDetail.gCaseID ON VR_CaseClient.gCaseId = VR_CaseDetail.gCaseID ON VR_CaseInvoiceDetail.gServiceItemID = VR_CaseServiceItem.gServiceItemID ON VR_CaseMember.gCaseId = VR_CaseDetail.gCaseID ON VR_CaseScript.gCaseID = VR_CaseDetail.gCaseID LEFT OUTER JOIN VR_case_provider ON VR_CaseServiceItem.gProviderID = VR_case_provider.gProviderID WHERE (VR_CaseService.iAlarmCenterID = 19) AND (VR_CaseAlarmCenter.CT_TRANSMISSION_TYPE LIKE N'%HCVP%') AND (VR_CaseAlarmCenter.iUpdateAC = 19) AND (VR_CaseDetail.nvchCaseNo = '1JNB014166') GROUP BY VR_ScriptQuestion.nvchQuestion, VR_CaseDetail.gCaseID, VR_CaseServiceItem.dtUpdateDate, VR_CaseMember.nvchLastName, VR_CaseMember.nvchFirstName, VR_CaseServiceItem.dAmount, VR_CaseServiceItem.CT_ITEM, VR_CaseClient.nvchCoName, VR_CaseScriptAnswer.nvchQuestion, VR_CaseScriptAnswer.nvchAnswer, VR_case_provider.provider_name, VR_CaseScript.gTemplateID, VR_CaseDetail.nvchCaseNo) DECLARE @query NVARCHAR(4000) SET @query = (SELECT * SELECT DISTINCT VR_CaseDetail.gCaseID, VR_CaseDetail.nvchCaseNo, VR_CaseServiceItem.dtUpdateDate, VR_CaseMember.nvchLastName, VR_CaseMember.nvchFirstName, VR_CaseServiceItem.dAmount, VR_CaseServiceItem.CT_ITEM, VR_CaseClient.nvchCoName, VR_CaseScriptAnswer.nvchQuestion, VR_CaseScriptAnswer.nvchAnswer, VR_case_provider.provider_name, VR_CaseScript.gTemplateID FROM VR_ScriptQuestion INNER JOIN VR_CaseScript ON VR_ScriptQuestion.gTemplateID = VR_CaseScript.gTemplateID INNER JOIN VR_CaseScriptAnswer ON VR_CaseScript.gScriptID = VR_CaseScriptAnswer.gScriptID INNER JOIN VR_CaseMember INNER JOIN VR_CaseInvoiceDetail RIGHT OUTER JOIN VR_CaseClient INNER JOIN VR_CaseAlarmCenter INNER JOIN VR_CaseDetail INNER JOIN VR_CaseService ON VR_CaseDetail.gCaseID = VR_CaseService.gCaseID INNER JOIN VR_CaseServiceItem ON VR_CaseService.gServiceID = VR_CaseServiceItem.gServiceID ON VR_CaseAlarmCenter.gCaseID = VR_CaseDetail.gCaseID ON VR_CaseClient.gCaseId = VR_CaseDetail.gCaseID ON VR_CaseInvoiceDetail.gServiceItemID = VR_CaseServiceItem.gServiceItemID ON VR_CaseMember.gCaseId = VR_CaseDetail.gCaseID ON VR_CaseScript.gCaseID = VR_CaseDetail.gCaseID LEFT OUTER JOIN VR_case_provider ON VR_CaseServiceItem.gProviderID = VR_case_provider.gProviderID WHERE (VR_CaseService.iAlarmCenterID = 19) AND (VR_CaseAlarmCenter.CT_TRANSMISSION_TYPE LIKE N'%HCVP%') AND (VR_CaseAlarmCenter.iUpdateAC = 19) AND (VR_CaseDetail.nvchCaseNo = '1JNB014166') GROUP BY VR_ScriptQuestion.nvchQuestion, VR_CaseDetail.gCaseID, VR_CaseServiceItem.dtUpdateDate, VR_CaseMember.nvchLastName, VR_CaseMember.nvchFirstName, VR_CaseServiceItem.dAmount, VR_CaseServiceItem.CT_ITEM, VR_CaseClient.nvchCoName, VR_CaseScriptAnswer.nvchQuestion, VR_CaseScriptAnswer.nvchAnswer, VR_case_provider.provider_name, VR_CaseScript.gTemplateID, VR_CaseDetail.nvchCaseNo) PIVOT ( MAX[nvchAnswer] FOR [(nvchQuestion)] IN (' + @columns + ') ) AS p EXECUTE(@query)
