c# - Turning a table "on it's side" in asp.net - how? -

how turn table:

+------------+-----------------+
| category + subcategory |
+------------+-----------------+
|cat..........+ persian.........|
|cat..........+ siamese........|
|cat..........+ tabby...........|
|dog.........+ poodle..........|
|dog.........+ boxer............|
+------------+----------------+

on it's side following:

+------------+-----------------+
| cat......... + dog............. |
+------------+-----------------+
+ persian..+ poodle.........+
+ siamese + boxer...........+
+ burmese + ...................+
+------------+-----------------+

the initial table following mysql query:

select c.categoryname, sc.name subcategorydefinition sc   join categorydefinition c on sc.categoryid = c.categoryid   c.isdeleted = 0   order categoryname, name asc 

and want display in (probably) gridview.

cheers!

pivot static in sql. need know in advance columns want in output, if list of categories not fixed, can't use pivot directly.

if using microsoft sql server (which know you're not, it's sake of example), use dynamic query in stored procedure, described here: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

now, in mysql, there no way execute dynamic sql on sql side (no equivalent of execute or sp_executeqsl), best choice generate similar sql query server-side (aspnet server-side).

another simpler idea imho forget doing in sql, aggregation in c# code.