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.

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

LVL 7
Jeff SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Remove the DISTINCT and the GROUP BY. The PIVOT clause is doing the "roll up" already.
0
Jeff SAuthor Commented:
I am still getting the same results and need to roll them up to one line.
0
ste5anSenior DeveloperCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.