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.
From the above table, I want to get only distinct records, with the education column concatenated. Something like this…
This can be achieved with a simple query…
SELECT DISTINCT FirstName, CAST(Concat_Education as nvarchar(max)) as Education_ctn
FROM EducationHistory p1
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.