拆解组装SQL字符串全过程---sql练习--转自Jinjazz（http://blog.youkuaiyun.com/jinjazz/archive/2009/09/01/4507720.aspx）

use tempdb go if (object_id ('t_Item' ) is not null ) drop table t_item go if (object_id ('t_Buy' ) is not null ) drop table t_Buy go create table t_Item (Item_ID int , Item_Name varchar (10 )) insert into t_Item select 1 , '面包' union select 2 , '衣服' union select 3 , '鞋子' create table t_Buy (Person varchar (10 ), WantBuy varchar (10 )) insert into t_Buy select '小张' , '1,2' union select '小王' , '1,2,3' select * from t_Item;select * from t_Buy; go /*原始表数据 Person WantBuy ---------- ---------- 小张 1,2 小王 1,2,3 */ /*要求查询结果 Person WantBuy ---------- ----------------- 小王 面包,衣服,鞋子 小张 面包,衣服 */ --t-sql-1 select Person , WantBuy = cast (replace (WantBuy , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' ) from (select distinct Person from t_Buy ) ta outer apply ( select WantBuy = (select WantBuy as v from ( select Person , c . Item_Name as WantBuy from ( select Person , convert (xml , '<v>' + replace (WantBuy , ',' , '</v><v>' )+ '</v>' ) as WantBuy from t_Buy )a outer apply ( select t . c . value ('.' , 'varchar(max)' ) AS WantBuy from a . WantBuy . nodes ('//v' ) AS t (c ) )b inner join t_Item c on b. wantbuy = c . item_id )d where person = ta . person for xml path ('' ) ) )tb --t-sql-2 with t1 as (select Person,CONVERT(xml,'<v>'+replace(WantBuy,',','</v><v>')+'</v>') as WantBuy from t_Buy) ,t2 as (select t1.Person,t2a.WantBuy from t1 outer apply (select t.c.value('.','varchar(max)') as WantBuy from t1.WantBuy.nodes('//v') as t(c)) t2a ) ,t3 as (select t2.Person,tI.Item_Name from t2 join t_Item tI on t2.WantBuy=tI.Item_ID) ,t4 as (select distinct Person,t4a.WantBuy from t_Buy tB cross apply (select WantBuy= (select Item_Name as v from t3 where Person=tB.Person for xml path('')) ) t4a ) ,t5 as (select Person,convert(xml,REPLACE(WantBuy,'</v><v>',',')).value('.','varchar(max)') as WantBuy from t4) select * from t5