Transpose Query Output

[rpnew]

Hi All,

It feels so good to be back on Bytes after almsot 2-3 years.. anyways here is my quesion. I would like to transpose my query output.

here are the details..


*** Tables
GDCA
gdcid,gdcid,gdaid,age


GDA
gdaid,name


*** Queries

SELECT
gda.id,age
FROM
gdca
JOIN gda ON (gda.id = gdca.gdaid)
WHERE gdca.gdcid IN(3573,184)
ORDER BY gdca.id

*** Output

"id" "age"
"1" "0"
"1" "11"
"2" "0"
"2" "30"
"3" "0"
"3" "1"
"4" "0"
"4" "1"
"5" "0"
"5" "1"

*** I want it something like

1 2 3 4 5
0 0 0 0 0
11 30 0 0 0

I tried using IF() clause or even CONCAT and then SUBSTRING_INDEX, however not getting the required output.

Any pointer is appreciated.

Thanks and Regards,
RP

[rpnew]

Any suggestions for above.. i need to complete one report?

[Rabbit]

I have no idea what you're trying to do. I don't see how you get to your results from your sample data.

[rpnew]

Okay.. let me explain a bit more..

I suppose output is not much difficult to understand.. now my requirement is to change column into rows..

right now query output is

id" "age"
"1" "0"
"1" "11"
"2" "0"
"2" "30"
"3" "0"
"3" "1"
"4" "0"
"4" "1"
"5" "0"
"5" "1"

Two columns one is for ID and another is for AGE.. now my requirement is something like this...
ID 1 2 3 4 5
AGE 0 0 0 0 0
AGE 11 30 0 0 0

In normal output if you see there are two rows for each ID. e.g. ID 1 has two values 0 and 11. In my requirement ID(s) should be column and related AGE should be rows, hence for ID 1 there would be two rows one containing value 0 and the other 11

@Rabbit

Thanks for the reply.. I think now the requirement should be clear..

Regards,
RP

[Rabbit]

For 3, 4, and 5, I still don't see why you have 0/0 in your output as opposed to 0/1.

[rpnew]

Hi Rabbit,

Yeah.. sorry my mistake.... you are right.. Required ouput is something like this..

ID 1 2 3 4 5
AGE 0 0 0 0 0
AGE 11 30 1 1 1

Regards,
RP

[Rabbit]

I don't know how you're grouping the records by row. I don't see anything in the data that indicates all the 0 records should be grouped together.

[rpnew]

Hi Rabbit,

let me put my ouput in another way...


"id" "age"
----------
"1" "0"
"2" "0"
"3" "0"
"4" "0"
"5" "0"
"1" "11"
"2" "30"
"3" "1"
"4" "1"
"5" "1"

And requirement is ...

ID 1 | 2 | 3 | 4 | 5
--------------------------
AGE | 0 | 0 | 0 | 0 | 0
--------------------------
AGE | 11 | 30 | 1 | 1 | 1

I have tried searching on google.. I got some suggestions for useing GROUP_CONCAT and STRING_INDEX. However did not get the exact output..

[Rabbit]

I still don't see what indicates that all the 0 records should be grouped together.

[rpnew]

Hi,

Let say.. first entries for each IDs should be first row... second entries for each IDs should be second row.. third entries of each IDs should be third row.. and so on..

Regards,
RP

[Rabbit]

MySQL has no way to distinguish the order in which a row was entered. It has to be user defined and I don't see a field in your post that defines the order.

[rpnew]

Hi Rabbit,

Agreed.. that there is now way to check the order in which the rows were entered but thats not the issue. We can for example sort the output by ID and use the output.

BTW, I got the output which i wanted by using GROUP_CONCAT() and then SUBSTRING_INDEX() however just wanted to know.. how much will this affect performance of qeury. Although, i dont see much issue there but just to get some idea..

Regards,
RP

[Rabbit]

GROUP_CONCAT and SUBSTRING_INDEX is fine for small amounts of records (a few thousand) but if you have a lot of records (hundreds of thousand), then you may want to think about using a cursor instead. But then again, fast and slow is different for different people.

[rpnew]

Okay.. but so far it looks okay.. The report will go into testing now so will see once it is tested.. and thanks for the reply once again..

Regards,
RP