First off, I actually believe that you'd be mixing logic and presentation layers if you go down the XSLT road to group data by categories.
Data storage and retrieval is separate, and since what you want to achieve has a direct relation to the meaning of this data, I'd say you should indeed do the grouping at this level: i.e. with a group by clause in the query, or in the class managing the ORM mapping.
An alternative view of things would be that the category data carries different meaning depending on how you are currently using the system, and as such it would be viable to do (or not to do) the grouping in your business logic. Still, if you do deal with it here, you might want to ask your data layer to handle the grouping for you anyway.
But, using the presentation layer to perform this grouping would be bad practice in my opinion.
Apart from that, I seriously doubt that achieving what you want through XSLT will be more efficient than letting the DB handle it for you. As such, the only performance reason you'd have to move from handling it in the DB would be if your DB is under so much stress that it can't do this properly. If your other server(s) have less stress, you could create a grouped array on the fly by iterating over the DB result resource, and it would be simple to do this.
Moving to the XSLT approach would let you not deal with it at the server level at all, if you instead rely on client side transformation, e.g. in a browser, which makes this the only valid point as I see it. But it comes with more issues.
I believe that most or all modern browsers do have XSLT support, but as far as I know, support is rather sketchy, and is also limited to XSLT 1.0, not 2.0. Without 2.0, you have no support for for-each-group which would probably make it easier.
Without this, one way would be to process the document once per existing category, and either create an XSLT document that contains all possible categories, wether they were in your query or not. Or you could issue an additional query (or run through the result set) to see what categoriese were in this query, and produce your XSLT on the fly as well.
As far as XSLT goes, it is identical no matter what processor you use. How you achieve the transformation depends on the processor used. As a crap example to achieve what you want by displaying data.xml in firefox
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="/tohtml_bygroup.xsl"?>
<items>
<item category="fruit">
<id>1</id>
<name>Oranges</name>
<category>fruit</category>
</item>
<item category="vegetable">
<id>2</id>
<name>Corn</name>
<category>vegetable</category>
</item>
<item category="fruit">
<id>3</id>
<name>Apples</name>
<category>fruit</category>
</item>
<item category="vegetable">
<id>4</id>
<name>Spinach</name>
<category>vegetable</category>
</item>
</items>
Which tells firefox to transform it by using tohtml_bygroup.xsl
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="items">
<html>
<head><title>Transformed</title></head>
<body>
<h3>Fruit</h3>
<xsl:for-each select="item[category='fruit']">
<div>
<xsl:value-of select="name"/>
</div>
</xsl:for-each>
<h3>Vegetable</h3>
<xsl:for-each select="item[category='vegetable']">
<div>
<xsl:value-of select="name"/>
</div>
</xsl:for-each>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
Perhaps there's a smarter way than this, but I'm not an algorithm genius, not was I ever very good with functional languages, and I know little XSLT as well.
But, back to my initial argument. You'd be better off generating a proper data structure to begin with, resulting in an XML that makes sense for your use case, i.e.
<?xml version="1.0" encoding="UTF-8"?>
<categories>
<category>
<name>fruit</name>
<items>
<item>
<id>1</id>
<name>Oranges</name>
</item>
<item>
<id>3</id>
<name>Apple</name>
</item>
</items>
</category>
<category>
<name>vegetable</name>
<items>
<item>
<id>2</id>
<name>Corn</name>
</item>
<item>
<id>4</id>
<name>Spinach</name>
</item>
</items>
</category>
</categories>
You can then use XSLT to produce whatever presentation you wish: plain text, html document, different layouts for different resolutions or print vs screen (which can also be achieved through CSS of course). To produce your unordered list of categories containing unodered lists of products, you'd only need to process the document once, and it might look like this
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="categories">
<html>
<head><title>Transformed</title></head>
<body>
<xsl:for-each select="category">
<h3><xsl:value-of select="name"/></h3>
<ul>
<xsl:for-each select="items/item">
<li>
<xsl:value-of select="name"/>
</li>
</xsl:for-each>
</ul>
</xsl:for-each>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
