SQLserver 动态拼接字符串 和字符串转换函数

SQLserver 小知识

1.动态拼接字符串

DECLARE @COL NVARCHAR(500)=N”,@SQL NVARCHAR(MAX)=N”,@XMMC NVARCHAR(200)=’微量元素测定’

SELECT @[email protected]+’[‘+COLNM+’],’ FROM LabCross.DBO.BASEINFO

WHERE [email protected]

SET @COL=SUBSTRING(@COL,1,LEN(@COL)-1)

SET @SQL=’

WITH T AS(

SELECT PatientName ,

PatientSourceName ,

SpecimenCollectionDateTime ,

ObservationName_EN,

ObservationNumberValue

FROM [LabCross_CDC].[dbo].[检验指标1]

WHERE ObservationGroupName =”’[email protected]+”’

)

SELECT PatientName ,

PatientSourceName ,

SpecimenCollectionDateTime ,’[email protected]+’

FROM T PIVOT( MAX(ObservationNumberValue) FOR ObservationName_EN IN (‘[email protected]+’ ) ) a

EXEC (@SQL)

2.CAST 函数用来转换字符串 ;

如果前值为空，则查后值，否则 查前值

CASE WHEN 前值条件 THEN 满足条件 ELSE 否则 END.

CASE WHEN _Lab_Result.ObservationOriginalValue =” THEN CAST(_Lab_Result.ObservationNumberValue AS NVARCHAR(100))

ELSE _Lab_Result.ObservationOriginalValue END AS ObservationNumberValue