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

How do I turn this table:

+------------+-----------------+

| Category + Subcategory |

+------------+-----------------+

|Cat..........+ Persian.........|

|Cat..........+ Siamese........|

|Cat..........+ Tabby...........|

|Dog.........+ Poodle..........|

|Dog.........+ Boxer............|

+------------+----------------+

on it's side to get the following:

+------------+-----------------+

| Cat......... + Dog............. |

+------------+-----------------+

+ Persian..+ Poodle.........+

+ Siamese + Boxer...........+

+ Burmese + ...................+

+------------+-----------------+

The initial table is from the following MySQL query:

select c.CATEGORYNAME, sc.NAME from subcategorydefinition sc
  join categorydefinition c on sc.CATEGORYID = c.CATEGORYID
  where c.ISDELETED = 0
  order by CATEGO开发者_JAVA技巧RYNAME, NAME ASC

And I want to display it in (probably) a Gridview.

Cheers!

Pivot is static in SQL. You need to know in advance the columns you want in output, so if the list of categories is not fixed, you can't use pivot directly.

If you were using Microsoft SQL Server (which I know you're not, but it's for the sake of example), you could use a dynamic query in a stored procedure, as described here: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Now, in MySql, there is no way to execute dynamic SQL on the sql side (no equivalent of EXECUTE or sp_executeqsl), so your best choice would be to generate a similar SQL query server-side (aspnet server-side).

Another simpler idea IMHO would be to forget about doing it in SQL, but to do the aggregation in your C# code.

You should use pivot

To do this in SQL, you'd need to dynamically generate your query based on the available set of values in the "Category" column. This is usually fairly painful and error prone, regardless of whether you do it in pure SQL (in a sproc) or in code (dynamic SQL).

I'd recommend reading your values from the database in the way that they are stored, then dynamically creating a DataTable or similar structure to use as the datasource for your UI.

I don't have a working version of MySql handy but this will work as long as there is always more cats than dogs because of the left join at the end of the script. I forgot that there isn't a full outer join in MySql but you could use this logic to try it out.

But the point of this is that if you have two tables with arbitrary keys you can join on the keys to get the results lined up like you want.

-- drop tables
 DROP TABLE dbo.cat
 DROP TABLE dbo.dog

 --create dog table
 create table dog (
 dog_id int IDENTITY(1,1) NOT NULL
 ,dog varchar(50)
 )

 --add dogs only
 insert into dog (dog)
 select subcategory 
  FROM play.dbo.test 
 where category = 'Dog'

 --create cat table
 create table cat (
 cat_id int IDENTITY(1,1) NOT NULL
 ,cat varchar(50)
 )

 --add cats only
 insert into cat (cat)
 select subcategory 
  FROM play.dbo.test 
 where category = 'cat'

 -- disply everything
 SELECT cat 
      , dog
  from dog d
 --full outer join cat c
 left join dog d
 on d.dog_id = c.cat_id