Click here to Skip to main content
15,922,315 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have a mysql table like below..

TrID	AccID	Commision
1	10	0.2
1	11	0.5
1	12	0.1
2	11	0.2
2	12	0.4
3	10	0.8


Now, i need to write one mysql query to get the out put like below..
TrID	Comm_10	Comm_11	Comm_12
1	0.2	0.5	0.1
2		0.2	0.4
3	0.8		



Please guide me.. how i can get this..

Thank you...
Posted

Hello, I hope the following links could be useful:
1. MySQL/Pivot table[^]
2. Pivot tables (crosstabs)[^]
3. The power of MySQL’s GROUP_CONCAT[^]
4. MySQL GROUP_CONCAT() function[^]
 
Share this answer
 
v2
Assuming your table is called table1, create 2 views:
SQL
create view table1_extended as (
  select
    trid,
    case when accid = 10 then commision end as Comm_10,
    case when accid = 11 then commision end as Comm_11,
    case when accid = 12 then commision end as Comm_12
  from table1
);

create view table1_extended_pivot as (
  select
    trid,
    sum(Comm_10),
    sum(Comm_11),
    sum(Comm_12)
  from table1_extended
  group by trid
);


Then, call:
select  * from table1_extended_pivot
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900