SQL server 将多行记录合并为一行（JSON格式）

CREATE PROC usp_kbjcrmjk_HCRM_GETLABREPORTNORMAL
(
	@lab_no NUMERIC(18,0)	
)
as 

BEGIN
	set nocount on
	
	select 
		distinct a.APPLYNO as lab_no ,
		'['+stuff(
		( select 
			',{"lab_item_code":"' + cast(a.HISORDERCODE as varchar)+'","lab_item_desc":"'+a.HISORDERNAME+
			'","lab_item_desc_en":"'+''+ '","item_code":"'+b.ITEMNUM +'","item_desc":"'+b.ITEMNAME+
			'","item_desc_en":"'+''+'","item_result_value":"'+b.RESULT+'","item_result_unit_desc":"'+b.UNIT+
			'","item_result_unit_desc":"'+b.UNIT+'","refer_highest_value":"'+''+'","refer_lowest_value":"'+''+
			'","refer_range_desc":"'+''+'","result_value_remark":"'+b.HIGHLOWFLAG+ +'"}'
		  from LIS_LIST a (nolock) INNER JOIN LIS_RESULT b (nolock) ON a.APPLYNO = b.APPLYNO 
		  where a.APPLYNO = @lab_no
		  for XML path('')    ), 1, 1, '') +']'as item
	from LIS_LIST a (nolock) INNER JOIN LIS_RESULT b (nolock) ON a.APPLYNO = b.APPLYNO 
	where a.APPLYNO = @lab_no
	
	IF @@ERROR <> 0 
	BEGIN
		SELECT 'F' AS error_code,'查询出错' AS error_message
		RETURN
	END
	set nocount off
end
go

1、stuff函数作用：

stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

2、参数

• param1：一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。
• startIndex：一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。
• length：一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。
• param2,返回类型。如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。

3、执行结果

[{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"FLGIgM","item_desc":"抗副流感病毒IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"FYYYT","item_desc":"抗肺炎衣原体IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"FYZYT","item_desc":"抗肺炎支原体IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"HBBD-IgM","item_desc":"抗呼吸道合胞病毒IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"JTJIgM","item_desc":"抗嗜肺军团菌IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"LGAIgM","item_desc":"抗流感病毒A型IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"LGBIgM","item_desc":"抗流感病毒B型IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}{"lab_item_code":361024","lab_item_desc":"呼吸道病原体谱抗体IgM检测(组合)","lab_item_desc_en":"","item_code":"XBDKT","item_desc":"抗腺病毒IgM抗体","item_desc_en":"","item_result_value":"阴性","item_result_unit_desc":"","item_result_unit_desc":"","refer_highest_value":"","refer_lowest_value":"","refer_range_desc":"","result_value_remark":" "}]