CREATE TABLE T2(rwdh int,tzsj datetime,tzry nvarchar(20),tbz nvarchar(max)) INSERT T2 SELECT 3210,'2010-11-11','张三','{粉煤灰(II级)=100}{矿粉(S95)=60}{砂2(中砂)=664}{石子1(碎卵石)=560}{水(自来水)=120}{外加剂2(FDN-A)=6.10}{石子3(碎卵石)=600}{水泥2(P.O42.5)=200}' WITH Ailly AS ( SELECT rwdh,tzsj=convert(varchar(10),tzsj,120),tzry, T.c.value('Name[1]', 'varchar(10)') as 材料, T.c.value('Quantity[1]', 'float') as 数量 FROM( select rwdh,tzsj,tzry, doc=cast( '<tbz>'+replace(replace(replace(replace(tbz,'{','<Item><Name>') ,'(','</Name><Type>'),')=','</Type><Quantity>'),'}','</Quantity></Item>')+'</tbz>' as xml) from T2 ) TB cross apply doc.nodes('/tbz/Item') T(c) ),CTE AS ( SELECT rwdh,tzsj,tzry,数量, 材料=(CASE patindex('%[0-9]%',材料) when 0 then 材料 ELSE SUBSTRING(材料,1,patindex('%[0-9]%',材料)-1) END) FROM Ailly ) SELECT * FROM CTE PIVOT ( SUM(数量) FOR 材料 IN ([粉煤灰], [矿粉], [砂], [石子], [水],[水泥],[外加剂]) ) T rwdh tzsj tzry 粉煤灰 矿粉 砂 石子 水 水泥 外加剂 3210 2010-11-11 张三 100 60 664 1160 120 200 6.1 (1 行受影响)