x

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)
more ▼

asked Jul 25, 2011 at 12:49 AM in Default

avatar image

Henkvl
11 1 1 2

Hi @Henkvl, please provide us with some sample data and the query you have at the moment and we can start helping you out.

Jul 25, 2011 at 12:54 AM WilliamD

Thank you. I am trying to upload the result set but gets an error....

Jul 25, 2011 at 01:20 AM Henkvl

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.

Jul 25, 2011 at 01:21 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018
x1066
x103

asked: Jul 25, 2011 at 12:49 AM

Seen: 1012 times

Last Updated: Jul 25, 2011 at 05:05 AM

Copyright 2016 Redgate Software. Privacy Policy