SQL code:
DECLARE @s VARCHAR(100)
SET @s='a,b,c,dd,ee,f,aa,a,aa,f'
SELECT b.v from
(SELECT CAST('<r>' + REPLACE(@s,',','</r><r>') + '</r>' AS XML) x) a
CROSS APPLY
(SELECT v=t.x.value('.','VARCHAR(10)') from a.x.nodes('//r') AS t(x) ) b
Õâ¸öµÄfcuandy·¢±íµÄÆäÖÐÒ»¶Î´úÂ룬ÆäÖеĺóÃæÎÒÓе㲻Ã÷°×£¬ºóÃæ×îºóµÄa£¬bÊÇʲôÒâ˼£¬»¹ÓÐSELECT b.v fromÖÖµÄb.vÓÖÊÇʲôÒâ˼£¿
'a,b,c,dd,ee,f,aa,a,aa,f'
Õâ¸ö×Ö·û´®.ʹÓÃCAST(' <r>' + REPLACE(@s,',',' </r> <r>') + ' </r>' AS XML)
µÃµ½
<r>a </r>
<r>b </r>
<r>c </r>
<r>dd </r>
....
È»ºócross apply(SELECT v=t.x.value('.','VARCHAR(10)') from a.x.nodes('//r') AS t(x) ) b
xmlÀàÐ͵Änodes·½·¨½«²úÉúÐм¯.
È»ºóµÃµ½
a
b
c
dd
ee
...
...
µÄ½á¹û.
a,bÊDZíµÄ±ðÃû.
b.v ÊÇb±íÁÐv
¶÷£¬½á¹ûÎÒÊÇÖªµÀ£¬¾ÍÊDz»ÖªµÀºóÃæµÄÄǸöa£¬bÊÇʲôÒâ˼
»¹ÓÐSELECT b.v fromºÍv=t.x.value·Ö±ð×öºÎ½âÊÍ£¡
ÀÏ´óµÄÊéÉÏÃæ½²µÃ±È½ÏÏêϸ ¿ÉÒÔÈ¥¿´¿´
ÉîÈëdz³ö SQL 2005¿ª·¢,¹ÜÀíÓëÓ¦ÓÃʵÀý
ÒýÓà ÀÏ´óµÄÊéÉÏÃæ½²µÃ±È½ÏÏêϸ ¿ÉÒÔÈ¥¿´¿´
ÎÒÓжà¸ö±íA B C ½á¹¹ÊÇÒ»ÑùµÄ£¬¶¼ÓÐ2¸ö×Ö¶ÎnameºÍcontent,ÎÒÒªËÑË÷ËùÒÔ±íËùÓÐ×Ö¶ÎÖаüº¬¡°Öйú¡±µÄÄÚÈÝ Õâ¸öSQLÓï¾äÔõôд ÈçºÎ½«¶à¸ö±íµÄ²éѯ½á¹ûÁ¬³ÉÒ»¸ö±í Õâ¸öSQLÓï¾äÔõôд ±íµÄ½á¹¹ÊÇÒ»ÑùµÄ ÀýÈç£ ......
ÎÒÒª×öÒ»¸ö¶¨µãʼþ£¬ÊÇÿÌìÍíÉÏ12µã¾Í°ÑÈý¸ö±íÀïµÄÐÅÏ¢·ÅÈëÒ»¸ö±íÀÎÒÏëÓô洢¹ý³ÌÀ´Ð´£¬ÔõôдÄØ£¿£¬ÄÇλ¸ßÊÖ°ï°ïæ°É£¬·Ç³£¸Ðл£¡ Óû§ÃûÊÇΨһµÄ ±íA ×ֶΠÓû§Ãû£ºName ,ÓÊÏ䣺Ema ......