SqlServer 查询结果合并成一行，复制到Excel，并显示序号

参考资料：

https://www.cnblogs.com/blazeZzz/p/7359575.html

https://www.cnblogs.com/zhang502219048/p/10989296.html

 

步骤：

1、将要查询的列的结果使用分组合并字符串 FOR XML PATH('')。如：SELECT  STUFF(( SELECT  ',' +convert(VARCHAR, USER_NAME) FROM TE_USER FOR XML PATH('')), 1, 1, '') AS UserName

2、将要查询的列结果加上英文的双引号：'"'+ xxx + '"'

3、添加ROW_NUMBER()，作为序号。添加char(10)，作为换行符。

 

SELECT  
T1.[ORDER_NO] AS 订单号
,T1.[INVENTORY_NO] AS 存货编码
,T1.[INVENTORY_NAME] AS 存货名称
,T2.[LASTMODIFY_DATE] AS 报检时间
,T3.[PLANCHECK_DAY] AS 计划检测天数
,ISNULL(T1.EXCEPTION_EVENT,'') AS 订单异常事件
,
'"'+
ISNULL(( 
	SELECT STUFF(
	(
		SELECT ' ' + [CHECK_EVENT] FROM 
		(
			SELECT ( '检验事件'+ cast(Row as nvarchar(5)) + ':'   + [CHECK_EVENT] + '   '+char(10)) AS [CHECK_EVENT] FROM
			(
				SELECT  ROW_NUMBER()over(order by [ID]) Row, [CHECK_EVENT]  
				FROM [TB_OQCMEMBER] AS TMP_TB1 WHERE TMP_TB1.OACPLAN_ID = T3.ID
			) AS TT
		) AS  #temp for xml path('')
	), 1, 1, '')
),'')
+'"
' AS 检验事件
FROM  [TB_ORDER_BASE] AS T1
LEFT JOIN [TB_ASSEMBLY] AS T2 ON T1.ORDER_ID = T2.ORDER_ID
LEFT JOIN [TB_OQC] AS T3 ON T2.ASSEMBLY_ID = T3.ASSEMBLY_ID

　　

 

将查询结果复制到EXCEL中，显示如何下：