By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Transpose Query Output

rpnew
100+
P: 188
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
Nov 15 '11 #1
Share this Question
Share on Google+
13 Replies


rpnew
100+
P: 188
Any suggestions for above.. i need to complete one report?
Nov 15 '11 #2

Rabbit
Expert Mod 10K+
P: 12,364
I have no idea what you're trying to do. I don't see how you get to your results from your sample data.
Nov 15 '11 #3

rpnew
100+
P: 188
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
Nov 15 '11 #4

Rabbit
Expert Mod 10K+
P: 12,364
For 3, 4, and 5, I still don't see why you have 0/0 in your output as opposed to 0/1.
Nov 15 '11 #5

rpnew
100+
P: 188
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
Nov 15 '11 #6

Rabbit
Expert Mod 10K+
P: 12,364
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.
Nov 15 '11 #7

rpnew
100+
P: 188
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..
Nov 15 '11 #8

Rabbit
Expert Mod 10K+
P: 12,364
I still don't see what indicates that all the 0 records should be grouped together.
Nov 15 '11 #9

rpnew
100+
P: 188
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
Nov 15 '11 #10

Rabbit
Expert Mod 10K+
P: 12,364
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.
Nov 15 '11 #11

rpnew
100+
P: 188
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
Nov 16 '11 #12

Rabbit
Expert Mod 10K+
P: 12,364
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.
Nov 16 '11 #13

rpnew
100+
P: 188
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
Nov 16 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.