拆解组装SQL字符串全过程
先看下面这段代码, 它将sql字符串先分割为行集，做一定处理后再合并为单行：<div class="source" style=""> 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'
go
/*原始表数据
Person WantBuy
---------- ----------
小张 1,2
小王 1,2,3
*/
/*要求查询结果
Person WantBuy
---------- -----------------
小王 面包,衣服,鞋子
小张 面包,衣服
*/
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
页:
[1]