CREATE PROC [dbo].[sp_GetSubjectivesAndNotesBySurfaceItemId]
	@surfaceItemId INT
AS

DECLARE @cols AS NVARCHAR(MAX),
		@query  AS NVARCHAR(MAX)

SELECT	@cols = STUFF((SELECT ',' + QUOTENAME(complaint) 
FROM	pal_vMedicalPatientSubjectiveDetails 
WHERE	surfaceItemId = @surfaceItemId
group by complaint, complaintID, surfaceItemId
order by complaintID
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
	
IF(@cols IS NULL)
BEGIN
	SELECT '' AS Date
			,'' AS Score
			,'' AS Complaint
			,'' AS Note
END
ELSE
BEGIN	
set @query = N'SELECT
			*
			FROM
			(
				SELECT	date	Date
						,score	Score
						,complaint Complaint
						,note	Note					
				FROM	pal_vMedicalPatientSubjectiveDetails 
				WHERE	surfaceItemId = '+CAST(@surfaceItemId AS VARCHAR)+'				
				) DataTable
			PIVOT
			(
				MAX(score)
				FOR complaint
				IN 
					(
						' + @cols + '
					)
			) PivotTable
			ORDER BY date
			'

exec sp_executesql @query;
END
--sp_GetSubjectivesBySurfaceItemId 5124
GO
