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 '11 at 12:49 AM in Default

Henkvl gravatar 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 '11 at 12:54 AM WilliamD
Thank you. I am trying to upload the result set but gets an error....
Jul 25 '11 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 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1933
x977
x76

asked: Jul 25 '11 at 12:49 AM

Seen: 722 times

Last Updated: Jul 25 '11 at 05:05 AM