求高手：sql内容转换
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)
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [id] int, [msg] varchar(100));
insert #temp
select '1','msg1=a msg2=b msg3=c' union all
select '2','msgx=aa msg2=bbb msg1=c msg3=c'
--SQL:
SELECT * FROM
(
SELECTid, fieldname = LEFT(b.msg, CHARINDEX('=', b.msg)-1), [value]=RIGHT(b.msg, LEN(b.msg)-CHARINDEX('=', b.msg))
FROM
(SELECT id, msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),' ','</v><v>')+'</v></root>') FROM #temp) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
) m
PIVOT
(MAX([value]) for fieldname IN([msg1], [msg2], [msg3])) n
/*
idmsg1msg2msg3
1abc
2cbbbc
*/