Apply的两个应用

APPLY比较实用，下面看两个应用。

1，字符连接

使用AdventureWorkDW2008R2

select c.ProductCategoryKey, b.name
from dbo.DimProductCategory c
cross apply(
select ','+CONVERT(nvarchar(20),s.EnglishProductSubcategoryName)
from dbo.DimProductSubcategory s
where c.ProductCategoryKey=s.ProductCategoryKey
order by
s.EnglishProductSubcategoryName
for xml path (''),type
) a(n)
cross apply(
select STUFF(a.n.value('./text()[1]','nvarchar(max)')
,1,1,'')
) b(name)
order by
c.ProductCategoryKey

结果如下

2，XML提取表

<students>
<student name="Bush">
<subjects>
<subject name="Math" score="10"/>
<subject name="English" score="20"/>
<subject name="Science" score="30"/>
</subjects>
</student>
<student name="Obama">
<subjects>
<subject name="Math" score="15"/>
<subject name="English" score="25"/>
<subject name="Science" score="35"/>
</subjects>
</student>
</students>

select t.student,t.subject,t.score
from @x.nodes('./students/student') student (rowset)
cross apply student.rowset.nodes('./subjects/subject') subject (rowset)
cross apply (
select student.rowset.value('@name','nvarchar(20)'),
       subject.rowset.value('@name','nvarchar(20)'),
       subject.rowset.value('@score','int')
       ) t (student,subject,score)
 order by     
t.student asc,
t.score desc;

结果如下