I have a data set that looks something like this:
Gender | Age | Name
Male | 30 | Bill
Female | 27 | Jenny
Female | 27 | Debby
Male | 44 | Frank
And I'm trying to display this as specially formatted HTML code:
<ul>
<li>Male
<ul>
<li>30
<ul>
<li>Bill</li>
</ul>
</li>
<li>44
<ul>
<li>Frank</li>
</ul>
</li>
</ul>
</li>
</ul>
<ul>
<li>Female
<ul>
<li>27
<ul>
<li>Jenny</li>
<li>Debby</li>
</ul>
</li>
</ul>
</li>
</ul>
I tried using FOR XML
but that didn't give the results I was looking for. It didn't remove the multiple Gender
and Age
fields returned. As you can see in this HTML it is compounding it all and only giving duplicates at the end node.
How would something like this be achieved in SQL Server?
See Question&Answers more detail:os