先排序 在string_agg

-------参考

SET NOCOUNT ON 

IF OBJECT_ID('TEMPDB..#TestTable') IS NOT NULL DROP TABLE #TestTable

CREATE TABLE #TestTable(

[A] [nvarchar](30) NULL,

[B] [nvarchar](30) NULL

);

with a as (

select '1' [a],'1吨' [B] union all

select '1' [a],'50米' [B] union all

select '1' [a],'4只' [B] union all

select '2' [a],'50米' [B] union all

select '2' [a],'10吨' [B] union all

select '2' [a],'4只' [B] union all

select '3' [a],'4只' [B] union all

select '3' [a],'1吨' [B] union all

select '3' [a],'50米' [B] union all

select '4' [a],'50米' [B] union all

select '4' [a],'4只' [B] union all

select '4' [a],'1吨' [B]

)

INSERT INTO #TestTable

SELECT * FROM A  ORDER BY A,(CASE WHEN B LIKE '%吨%' THEN 1 WHEN B LIKE '%米%' THEN 2  ELSE 3 END) asc

SELECT A,STRING_AGG(B,',') FROM #TestTable GROUP BY A

DROP TABLE #TestTable

你拼好后，还要单独列出吨前面的数字？就比如第一行   323.00吨XXXX内容   你还要一个单独是323的结果？