Pivoting Columns that Contain Nulls

Good Morning All,

I have a table, EncounterDiag, that includes fields [Encounter Number], [Secondary Diagnosis], [Secondary Diag Sequence], and [Present on Admission Flag]. I need to pivot the [Secondary Diagnosis] and [Present on Admission Flag] fields for up to 49 diagnoses per encounter. Some records might not have 49 diagnoses, most will have fewer than that. Basically, I want the columns of my end result to look like:

Encounter Number DX1 DX2 DX3 DX4 DX5 .... POA1 POA2 POA3 ...

I've written the following stored procedure as a starting point:

ALTER PROCEDURE [dbo].[getClaroPtDetail] 
	-- Add the parameters for the stored procedure here
	@StartDate varchar(15), @EndDate varchar(15)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
	Declare @columns varchar(8000), @query varchar(8000)	

	select @columns = coalesce(@columns + ',[' +cast([Encounter Number] AS VARCHAR) + ']',
	'[' +CAST([SECONDARY DIAGNOSIS] AS VARCHAR)+ ']')
	FROM EncounterDiag
	Group by [Encounter Number], [Secondary Diagnosis]

	set @query = 
	'Select [Encounter Number], [Secondary Diagnosis]
	from EncounterDiag
	Pivot
	(
	MAX([Secondary Diagnosis])
	for
	[Encounter Number]
	in (' +@columns + ')
	)
	as p'
Execute @query
END

When I run this stored procedure, I receive an error stating:

The name 'Select [Encounter Number], [Secondary Diagnosis]

from EncounterDiag

Pivot

(

MAX([Secondary Diagnosis])

for

[Encounter Number]

in ([873.42],<Numerous Encounter Numbers omitted due to PHI>is not a valid identifier.

How would I modify the stored procedure to correct this error?

Thanks,

Sid

Pivoting Columns that Contain Nulls - Coalesce?