question

Henkvl avatar image
Henkvl 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)
sql-server-2005t-sqlpivot
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi @Henkvl, please provide us with some sample data and the query you have at the moment and we can start helping you out.
1 Like 1 ·
Thank you. I am trying to upload the result set but gets an error....
0 Likes 0 ·
You only need to give us the T-SQL for your query and if possible the create script for the table with a little test data and expected results. If you can't edit your answer, just add it as a comment and a moderator will help sort that out for you.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.