cross apply in SQL

If the we have a data of this sort in a table

Mod no | Name | Unit Code

270 | Rajani | AA

270 | Rajani | BB

270 | Rajani | cc

271 |Rajani |AA

271 | Rajani | BB

And if we want the data to be displayed as

270 |Rajani | aa,bb,cc

271 |Rajani | aa,bb

Then the following query can be written and its allowed in 2008… don’t know about 2005.

sELECT  distinct    a.Loc_Icardallotter,                    LEFT(el.EmpList,LEN(el.EmpList)-1)

FROM         Mlocationwisealloter a

cross apply ( select loc_fullname +' , ' as [text()]

from Mlocationwisealloter b inner join mlocation on loc_code = b.loc_unitcd where b.loc_moduleno = a.loc_moduleno and b.Loc_Icardallotter = a.Loc_Icardallotter

order by loc_fullname

FOR XML PATH(''))el (EmpList)

WHERE     (Loc_moduleno = 270)