Rotating a table "on it" in asp.net - how?

Question

How to include this table:

+ ------------ + ----------------- +
| Category + Subcategory | + ------------ + ----------------- +
| Cat .......... + Persian ......... | | Cat .......... + Siamese ........ | | Cat .......... + Tabby ........... |
| Dog ......... + Poodle ..........

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

on it to get the following:

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

+ ------------ + ----------- ------ +
+ Persian .. + Poodle ......... +
+ Siamese + boxer ........... +
+ Burmese + ........ ........... +
+ ------------ + ----------------- +

The source table is shown in 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 CATEGORYNAME, NAME ASC

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

Hooray!

Answer 1

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

Microsoft SQL Server (, , , ), , : http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

, MySql, SQL SQL ( EXECUTE sp_executeqsl), SQL- ( aspnet).

IMHO , SQL, #.

Answer 2

pivot

Answer 3

SQL, "". , , SQL ( sproc) ( SQL).

, , DataTable .

Answer 4

MySql, , , , - script. , MySql , , .

, , , , , .

-- 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