SQL columns based on select values

Question

Would like to get data with preferably one query, but columns are dynamical based on selected values.

So my tables look like this (using MySql):

CREATE TABLE 'users' (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL
)

CREATE TABLE `income` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` int(11) NOT NULL,
  `day` int(11) NOT NULL DEFAULT '0',
  `action_a` decimal(10,2) NOT NULL DEFAULT '0.00',
  `action_b` decimal(10,2) NOT NULL DEFAULT '0.00',
  `action_c` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
)

CREATE TABLE `given` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `day` int(11) DEFAULT NULL,
  `to_user` int(11) NOT NULL,
  `from_user` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
)

Would like to get in one query table that look something like this:

+------------+--------------+----------+----------+----------+
| day | action_a | action_b | action_c | user_B   | user_C   |
+------------+--------------+----------+----------+----------+
| 123 | 123.01   | 123.01   | 123.01   |  123.01  | -123.01  |
| 122 | 324.02   | 234.01   | 123.01   | -123.01  | -123.01  |
| 121 | 987.00   | 345.01   | 123.01   |  123.01  | -123.01  |
| 120 | 9393.01  | 456.01   | 123.01   | -123.01  | -123.01  |
| 119 | 0.00     | 567.01   | 123.01   | -123.01  | -123.01  |
 ...

Negative values in user columns means user gave to other user

Currently I'm doing 3 separate queries and afterwards merging data, but my current queries look like this:

Get action based income

Select action_a, action_b, action_c From income i Join users u on u.id=i.user Where u.name='%s' and i.day=%i Order by i.id desc

Get received

Select u2.name 'from', amount From given g Join users u on u.id = to_user Join users u2 on u2.id = from_user Where u.name='%s' and amount <> 0.0 Order by g.id 

Get given

Select u2.name 'to', amount From given g Join users u on u.id = from_user Join users u2 on u2.id = to_user Where u.name='%s' and amount <> 0.0 Order by g.id desc

SQLFiddle link

Answer 1

An example of normalised income/income_action tables might be as follows:

CREATE TABLE income 
(income_id SERIAL PRIMARY KEY
,dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
,user INT NOT NULL
);

CREATE TABLE income_action 
(income_id INT NOT NULL
,action CHAR(1) NOT NULL
,value DECIMAL(10,2) NOT NULL DEFAULT '0.00',
,PRIMARY KEY(income_id,action)
);