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;

结果如下