table（id,msg）分割提取转化成 table1(msg1,msg2,msg3) 一条对应一条，主要是要求转换msg数据。
如：
id msg
1 msg1=a msg2=b msg3=c ...
2 msgx=aa msg2=bbb msg1=c msg3=c ..
说明：msg数据格式不统一(msg1,msg2顺序不唯一，长度不固定，msg的长度不固定）可以肯定的是msg中要分割的数据都已用空格隔开。
我的思路是，先将msg按空格分割提取，一条数据变成一列3行。再用for xml path('')再转回一行。在一条数据是可以成功，但拿一个表数据就不知道该怎么办了，也许我的思路是不行的。所以在此求助下高手：给些关键思路也好。
之前还有个思路：sql函数 GetMsg(src,msg1),可以拿到msg1在src的下标，但我拿不到下一个空格下标。
------解决方案--------------------
--分解字符
WITH a1 (id,msg) AS
(
SELECT 1,'msg1=a msg2=b msg3=c' UNION ALL
SELECT 2,'msgx=aa msg2=bbb msg1=c msg3=c'
)
,a2 AS
(SELECT id,','+REPLACE(msg,' ',',')+',' cstr FROM a1)
,a3 AS
(
SELECT id,cstr,CHARINDEX(',',cstr,1) n1,1 n2
FROM a2
UNION ALL
SELECT id,cstr,CHARINDEX(',',cstr,n1+1) n1,n2+1
FROM a3
WHERE CHARINDEX(',',cstr,n1+1)>0
)
,a4 AS
(SELECT id,cstr,n1,(SELECT n1 FROM a3 WHERE cstr=a.cstr AND n2=a.n2+1) n2 FROM a3 a)
,a5 AS
(
SELECT id,SUBSTRING(cstr,n1+1,n2-n1-1) cstr FROM a4 WHERE n2 IS NOT NULL
)
SELECT id,LEFT(cstr,CHARINDEX('=',cstr)-1) cstr1,right(cstr,LEN(cstr)-CHARINDEX('=',cstr)) cstr2
INTO #temp
FROM a5
ORDER BY id
--組合動態SQL語句
DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX)
SET @colList = STUFF(
(
SELECT ','+QUOTENAME(cstr1)
FROM (SELECT DISTINCT cstr1 FROM #temp) t
FOR XML PATH('')
),1,1,'')
SET @sql = N'
select *
from #temp a
pivot
(max(cstr2) for cstr1 in('+ @colList +')) b
'
EXEC(@sql)
------解决方案--------------------