Dynamic SQL help

Jeff S
Jeff S used Ask the Experts™
on
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.

SQL Results
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

Author

Commented:
I am still getting the same results and need to roll them up to one line.
Senior Developer
Commented:
Then your table contains more data.. try

SET @DynamicPivotQuery = N'
    WITH Data AS (
        SELECT  DISTINCT
                PatientName ,
                PatientId ,
                InternalID ,
                ProfessionalID ,
                AssessmentDate ,
                AssessmentReasonCode ,
                InternalEpisodeCode ,
                InternalAssessmentID ,
                RecordIdentifier ,
                FieldName
        FROM    CusDARMHA_ImportExport
        )
    SELECT  *
    FROM    Data
    PIVOT   (   MAX(AnswerID)
                FOR FieldName
                IN ( ' + @ColumnName + ' )
            ) AS PVTTable;
';

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial