Concatenating row values in a single statement using CROSS APPLY

One more SQL issue, one more solution provided. I feel good about it now ;). I am adding it to my collection because it really helps in reducing the duration to execute the process. It is about a new feature which was introduced way back in SQL 2005. CROSS APPLY.

The Scenario: Let me lay down the scenario, I have a table which has some 100 thousand + records. This is a very (deliberately) de normalized table. The structure and contents of the table are as below.

ID	FirstName	Education
1	Vighnesh	BSC KUD 1995
2	Vighnesh	MSC KUD 1997
3	Vighnesh	PHD MIT 2009
4	Mark	BCOM KUD 2000
5	Mark	MBA IIMB 2008
6	Vicky	BE VIT 2008

From the above table, I want to get only distinct records, with the education column concatenated. Something like this…

ID	FirstName	Education
1	Vighnesh	BSCKUD 1995 MSC KUD1997 PHDMIT2009
4	Mark	BCOMKUD 2000MBAIIMB2008
6	Vicky	BE VIT2008

This can be achieved with a simple query…

SELECT DISTINCT FirstName, CAST(Concat_Education as nvarchar(max)) as Education_ctn
    FROM EducationHistory p1
 CROSS APPLY
 (
  SELECT cast(Education + '' as xml)
  FROM EducationHistory p2               
  where p2.Name = p1.Name
  ORDER BY Education
  FOR XML PATH(''), TYPE
 )
 V ( Concat_Education )

In the above query we are using FOR XML PATH(‘’), TYPE. If we don’t use this along with casting the Education column as xml (cast(Education + '' as xml)), we will get the output xml as “<” and “>”.
While searching for the possible solution, I searched the web with different approaches. This also shows the evolution in my thinking from start to end. Sample this:
1. assigning value to variable in sub query
2. can I use variable in sub query?
3. coalesce in sql server
4. combine value of 2 rows sql
5. combine value of 2 rows sql in DISTINCT query
6. concat subquery rows
7. concat using coalesce in subquery – here is where I got the answer (thanks to this blog)
8. FOR XML PATH('') < >
9. FOR XML PATH('') < with < get rid
10. FOR XML PATH('') is replacing < with <
11. FOR XML PATH('') sql server

Feel free to contact me in case you need help.
-Vighnesh Bendre