asked on

Dynamic SQL help

I need help rolling the results to one line for each unique patient per Assessment Date. The example is a test patient, no worries about a real patients data.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);
 
--Get distinct values of the PIVOT Column 
SELECT
	@ColumnName = ISNULL(@ColumnName + ',' , '') + QUOTENAME(FieldName)
	FROM (SELECT DISTINCT FieldName FROM CusDARMHA_ImportExport) AS Courses;

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = N'SELECT DISTINCT
         PatientName 
        , PatientId 
        , InternalID 
        , ProfessionalID 
        , AssessmentDate 
        , AssessmentReasonCode 
        , InternalEpisodeCode 
        , InternalAssessmentID 
        , RecordIdentifier  
        , '+@ColumnName+'
    FROM CusDARMHA_ImportExport
   
    PIVOT(MAX(AnswerID) 
          FOR FieldName IN ('+@ColumnName+')) AS PVTTable
     
          
          GROUP BY 
          PatientName 
        , PatientId 
        , InternalID 
        , ProfessionalID 
        , AssessmentDate 
        , AssessmentReasonCode 
        , InternalEpisodeCode 
        , InternalAssessmentID 
        , RecordIdentifier
        ,'+@ColumnName+' ';
--Execute the Dynamic Pivot Query

EXEC sp_executesql
	@DynamicPivotQuery;

Open in new window

ste5an

Remove the DISTINCT and the GROUP BY. The PIVOT clause is doing the "roll up" already.

Jeff S

ASKER

I am still getting the same results and need to roll them up to one line.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

Thanks