feb with dates

--first step: create columns list and save it to a variable
--create columns list for current language.
declare @PAT_Filter nvarchar(max)

--here is a new variable with filter for patient.id.
--Only one string starting with set @pat_filter must leave uncommented.

--for list. comment one, leave uncommented another.
set @PAT_Filter=N'AND PATIENT.PAT_ExternalId in  (''04772344'',''04772344'')'
--for like
--set @PAT_Filter=N'AND PATIENT.PAT_ExternalId like  (''%4772344'')'
--Uncomment string below to disable filter
--set @PAT_Filter=N''

--we declare a variable @list , initialize it with empty string.
--N letter before means that it will be in National Char, because sp_executesql needs only N' variable.
declare @list nvarchar(max)
set @list=N''
--subquery names 'As T' select all column names, simple join. outerquery concatenate string into a var @list
select  @list=@list+N',['+rtrim(SYMITEM_ShortText)+N']' from
--remove top10 later, it for testing
(select distinct  top 10  isnull(symitem_shorttext,'nothing') as symitem_shorttext  from PARA_DESC INNER JOIN
SYMBOLITEM ON PARA_DESC.SYM_Nr = SYMBOLITEM.SYM_Nr
--this shorttext is a bad for column names and must be removed
where SYMBOLITEM.LANGUAGE_Id = 1 and symitem_shorttext not in ('......','')) as t

--remove last 'comma'
set @list=substring(@list,2,len(@list)-1)
--print for debug
print @list
print @pat_filter

declare @q nvarchar(max)
set @q=N'select * from
(SELECT

PATIENT.PAT_ExternalId, PATIENT.PAT_Birthdate, PATIENT.PAT_FirstName,
PATIENT.PAT_Lastname, TEST.TEST_Weight, TEST.TEST_Height,
MEASPARAMETER.PARA_Value, MEASPARAMETER.PARA_PredValue,
SUBMEASURE.SUBMEAS_BestMarker,
PARA_DESC.PARADES_Type,
TEST.TEST_GenDate,

(select PROP_ValueN from PATIENT_ACTSETTING where COLPROP_No=1 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as weightp,
(select PROP_ValueN from PATIENT_ACTSETTING where COLPROP_No=2 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as heightp,
(select PROP_ValueS from PATIENT_ACTSETTING where COLPROP_No=9 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as diagnosis,
(select PROP_ValueS from PATIENT_ACTSETTING where COLPROP_No=10 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as location,
(select PROP_ValueS from PATIENT_ACTSETTING where COLPROP_No=11 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as doctor,
(select PROP_ValueS from PATIENT_ACTSETTING where COLPROP_No=12 and PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id) as location12,

(select cast(PARAGRAPH.DOCP_Text as varchar(max)) from PARAGRAPH where [DOCUMENT].DOC_Id = PARAGRAPH.DOC_Id and DOCP_Type=0 ) as Interpretation,
(select cast(PARAGRAPH.DOCP_Text as varchar(max)) from PARAGRAPH where [DOCUMENT].DOC_Id = PARAGRAPH.DOC_Id and DOCP_Type=24 ) as [Test quality],
PATIENT.PAT_ExternalId AS Expr2,
RTRIM(SYMBOLITEM.SYMITEM_ShortText) as ColumnName
FROM PATIENT INNER JOIN
TEST ON PATIENT.PAT_DemId = TEST.PAT_DemId INNER JOIN
MEASUREMENT ON TEST.TEST_Id = MEASUREMENT.TEST_Id INNER JOIN
SUBMEASURE ON MEASUREMENT.MEAS_Id = SUBMEASURE.MEAS_Id INNER JOIN
MEASPARAMETER ON SUBMEASURE.SUBMEAS_Id = MEASPARAMETER.SUBMEAS_Id INNER JOIN
PARA_DESC ON MEASPARAMETER.PARADES_Id = PARA_DESC.PARADES_Id INNER JOIN
SYMBOLITEM ON PARA_DESC.SYM_Nr = SYMBOLITEM.SYM_Nr
INNER JOIN PATIENT_ACTSETTING ON PATIENT.PAT_Id = PATIENT_ACTSETTING.PAT_Id INNER JOIN
                      [DOCUMENT] ON PATIENT.PAT_DemId = [DOCUMENT].PAT_DemId INNER JOIN
                      PARAGRAPH ON [DOCUMENT].DOC_Id = PARAGRAPH.DOC_Id
WHERE (SYMBOLITEM.LANGUAGE_Id = 1) AND (SUBMEASURE.SUBMEAS_BestMarker = 1)

'+@PAT_Filter+'
AND  TEST_GenDate between ''20080101'' and ''20090101''
) as sourcetable
pivot
(
max(para_value) for columnname in
('+@list+N')) as ptable
ORDER BY PAT_ExternalId'

print @q
exec sp_executesql @q