transpose data?

[mdemetri2]

Hi. I have a table that I been to summarise, but use its column / files as rows, in otherwords transpose the table. 

 

Structure example is:

 

id | month | CatA | CatB | CatC | CatD

1    jan       23     34    33     78

2    feb       21     38    67     78

2    mar       18     42    76     80

 

and would like to display:

 

      Jan  Feb Mar

CatA   23  21   18

CatB   34  38   67

CatC   33  67   76

CatD   78  78   80

 

Is this possible? Really appreciate any pointers. 

[Psycho]

Yes, it's absolutely possible. But,you are talking about how to "display" the data. You would just query the data and have logic (i.e. code) for determining the output to display as you wish. SO, this is really a PHP question (assuming you are using PHP).

 

Please show the code you have so far and the problems you have. I suspect this is a homework problem and don't want to just give you a solution. And, don't bother wasting your time telling me it is not a homework problem. I won't believe you.

 

EDIT: By the way, that DB schema is not very good. Having columns for CatA, CatB, CatC and CatD could be problematic. Would there always be exactly four categories for each record and no need to increase or decrease the number in the future? If no, then the schema is wrong. Also, is there a legitimate distinction between CatA, CatB, etc. or is it just a way to allow four categories? If there is no real significance between the four, then the schema is wrong.

 

Not to mention that you should be using a date field for the month instead of a string. What happens when the year changes? Plus, you can't ORDER the results correctly based on name.

 

The schema probably needs to be more like this:

id | date     | cat
1    2014-1-1   23
2    2014-1-1   34
3    2014-1-1   33
4    2014-1-1   78
5    2014-2-1   21
6    2014-2-1   38
7    2014-2-1   67
8    2014-2-1   78
etc . . .

Edited July 9, 2014 by Psycho

[mdemetri2]

Ok thanks Psycho, I was kinda coming to that conclusion myself about the schema being wrong....so I will take a look at amending it I think. 

 

Thank you so much for the tips!