在SQL中选择列值作为列标题？

Question

I have a table like this:

------------------------------------------------------
ID  |   Date   |  ClientName  |  TransactionAmount   |
------------------------------------------------------
1   |  6/16/13 |  C1          |  15                  |
------------------------------------------------------
2   |  6/16/13 |  C1          |  10                  |
------------------------------------------------------
3   |  6/16/13 |  C2          |  10                  |
------------------------------------------------------
4   |  6/17/13 |  C2          |  20                  |
------------------------------------------------------

And I would like to get something like this:

------------------------------------------------------------------------
Date    |  C1_Total_Amount_Transacted   |  C2_Total_Amount_Transacted  |
------------------------------------------------------------------------
6/16/13 |            25                 |              10              |
------------------------------------------------------------------------
6/17/13 |            0                  |              20              |

In the second table Date is unique also I there are x clients in the databse the resul table will have x + 1 columns (1 fore date and x one for each client).

There might be necessary to write some PHP code and more querys, any working solution is perfect, I don`t need a full SQL solution.

Thanks

Answer 1

I presume that you are rather new to SQL. This type of query requires conditional summation. And it is quite easy to express in SQL:

select `date`,
       sum(case when Client_Name = 'C1' then TransactionAmount else 0 end) as C1,
       sum(case when Client_Name = 'C2' then TransactionAmount else 0 end) as C2
from t
group by `date`

But, you have to list each client in the query. You always have to specify the exact column headers for a SQL query. If you don't know them, then you need to create the SQL as a string and then execute it separately. This is a rather cumbersome process.

You can often get around that by using group_concat(). This puts the values in a single column, with a separator of your choice (default is a comma):

select `date`, group_concat(amount)
from (select `date`, ClientName, sum(TransactionAmount) as amount
      from t
      group by `date`, ClientName
     ) t
group by `date`