0

Let say we have this table:

CREATE TABLE `table1` (
`Name` CHAR(50) NOT NULL,
`Type` CHAR(50) NOT NULL,
`Value` DOUBLE NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


 ---------------------------------
|   Name   |   Type   |   Value   | 
 ---------------------------------   
|   Dep1   |   Cash   |   100     | 
 ---------------------------------  
|   Dep1   |   Card   |   200     | 
 ---------------------------------  
|   Dep2   |   Cash   |   300     | 
 ---------------------------------  
|   Dep2   |   Card   |   400     | 
 ---------------------------------

How to create a MySQL select query to transform rows in columns like this? I must mention that column 'Name' has dynamic values!

 --------------------------------
|   Name   |   Cash   |   Card   |    
 --------------------------------   
|   Dep1   |    100   |    200   |     
 --------------------------------   
|   Dep2   |    300   |    400   |
 --------------------------------
ekad
  • 14,056
  • 26
  • 43
  • 45
David Ungurean
  • 21
  • 1
  • 3

1 Answers1

1
select name, 
       sum(case when `type` = 'Cash' then `value` else 0 end) as Cash,
       sum(case when `type` = 'Card' then `value` else 0 end) as Card
from your_table
group by name
juergen d
  • 195,137
  • 36
  • 275
  • 343