WITH a1 (msg) AS ( SELECT '001、电视、2、1900,002、手机、5、999,003、冰箱、3、1800' ) ,a2 AS ( SELECT b.*,ROW_NUMBER() OVER(ORDER BY GETDATE()) id FROM (SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a OUTER APPLY (SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b ) ,a3 AS ( SELECT b.*,ROW_NUMBER() OVER(ORDER BY GETDATE()) re FROM (SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a OUTER APPLY (SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b ) ,a4 AS ( SELECT id,b.msg,ROW_NUMBER() OVER(PARTITION BY id ORDER BY GETDATE()) id2 FROM (SELECT id, msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),'、','</v><v>')+'</v></root>') FROM a2) a OUTER APPLY (SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b ) select id,[1] as "编码",[2] as "名称",[3] as "数量",[4] as "价格" FROM a4 pivot ( max(msg) for id2 IN ([1],[2],[3],[4]) ) pvt
DECLARE @STR VARCHAR(8000) SET @STR='001、电视、2、1900,002、手机、5、999' SET @STR='<V><V1>'+REPLACE(REPLACE(@STR,'、','</V1><V1>'),',','</V1></V><V><V1>')+'</V1></V>' PRINT @STR DECLARE @XML XML SET @XML=CONVERT(XML,@STR) SELECT MAX(CASE WHEN (RN-1)%4=0 THEN V1 END)[编码] ,MAX(CASE WHEN (RN-1)%4=1 THEN V1 END)[名称] ,MAX(CASE WHEN (RN-1)%4=2 THEN V1 END)[数量] ,MAX(CASE WHEN (RN-1)%4=3 THEN V1 END)[价格] FROM( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,N.V.value('.','varchar(100)')V1 FROM @XML.nodes('//V1')N(V) )T GROUP BY (RN-1)/4再封闭成存储过程就可以了
DECLARE @Str NVARCHAR(max)='001、电视、2、1900,002、手机、5、999,003、冰箱、3、1800'SET @Str='WITH Product(编码,名称,数量,价格) as (select '''+REPLACE(REPLACE(@Str,',',''' union all select '''),'、',''',''')+''') select * from Product' EXEC(@Str) /* 编码 名称 数量 价格 001 电视 2 1900 002 手机 5 999 003 冰箱 3 1800 */
--比较麻烦的办法WITH a1 (msg) AS ( SELECT '001、电视、2、1900,002、手机、5、999,003、冰箱、3、1800'), a2 as (select row_number() over(order by getdate()) as rn,substring(msg,number,charindex(',',msg+',',number)-number) as msg from a1,master..spt_values where type='P' and number>=1 and number<=len(msg) and substring(','+msg,number,1)=',')select rn,rn1=convert(varchar,row_number() over (partition by rn order by getdate())),substring(msg,number,charindex('、',msg+'、',number)-number) as msg into #t from a2,master..spt_values where type='P' and number>=1 and number<=len(msg) and substring('、'+msg,number,1)='、'declare @s varchar(max)select @s=isnull(@s+',','')+'max(case when rn1='''+rn1+''' then msg end) as ['+rn1+']' from #t group by rn1set @s='select '+@s+' from #t group by rn' print @s exec(@s)drop table #t
WITH a1 (msg) AS
(
SELECT '001、电视、2、1900,002、手机、5、999,003、冰箱、3、1800'
)
,a2 AS
(
SELECT b.*,ROW_NUMBER() OVER(ORDER BY GETDATE()) id
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
,a3 AS
(
SELECT b.*,ROW_NUMBER() OVER(ORDER BY GETDATE()) re
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
,a4 AS
(
SELECT id,b.msg,ROW_NUMBER() OVER(PARTITION BY id ORDER BY GETDATE()) id2
FROM
(SELECT id, msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),'、','</v><v>')+'</v></root>') FROM a2) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
select id,[1] as "编码",[2] as "名称",[3] as "数量",[4] as "价格"
FROM a4
pivot
(
max(msg) for id2 IN ([1],[2],[3],[4])
) pvt
SET @STR='001、电视、2、1900,002、手机、5、999'
SET @STR='<V><V1>'+REPLACE(REPLACE(@STR,'、','</V1><V1>'),',','</V1></V><V><V1>')+'</V1></V>'
PRINT @STR
DECLARE @XML XML
SET @XML=CONVERT(XML,@STR)
SELECT
MAX(CASE WHEN (RN-1)%4=0 THEN V1 END)[编码]
,MAX(CASE WHEN (RN-1)%4=1 THEN V1 END)[名称]
,MAX(CASE WHEN (RN-1)%4=2 THEN V1 END)[数量]
,MAX(CASE WHEN (RN-1)%4=3 THEN V1 END)[价格]
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN,N.V.value('.','varchar(100)')V1 FROM
@XML.nodes('//V1')N(V)
)T
GROUP BY (RN-1)/4再封闭成存储过程就可以了
EXEC(@Str)
/*
编码 名称 数量 价格
001 电视 2 1900
002 手机 5 999
003 冰箱 3 1800
*/
( SELECT '001、电视、2、1900,002、手机、5、999,003、冰箱、3、1800'),
a2 as
(select row_number() over(order by getdate()) as rn,substring(msg,number,charindex(',',msg+',',number)-number) as msg from a1,master..spt_values
where type='P' and number>=1 and number<=len(msg) and substring(','+msg,number,1)=',')select rn,rn1=convert(varchar,row_number() over (partition by rn order by getdate())),substring(msg,number,charindex('、',msg+'、',number)-number) as msg into #t from a2,master..spt_values
where type='P' and number>=1 and number<=len(msg) and substring('、'+msg,number,1)='、'declare @s varchar(max)select @s=isnull(@s+',','')+'max(case when rn1='''+rn1+''' then msg end) as ['+rn1+']' from #t group by rn1set @s='select '+@s+' from #t group by rn'
print @s
exec(@s)drop table #t