CODEWITHSUNDEEP
mysql
Hakunamatata
Hakunamatata

Reputation: 46

Transforming Table data into new view

I has a SQL Query to select below result.

    |Port| Type|Size|
    _________________
    |JKT | D40 | 10 |
    |JKT | D50 | 20 |
    |ABC | D30 | 10 |
    |KFC | D50 | 20 |

I would like to put the result into the table like below , the JKT,ABC,KFC are not fixed .

     |D30 | D40 | D50   <<< this is fix in the table.
JKT  | 0  |  10 | 20
ABC  | 10 |  0  |  0
KFC  | 0  |  0  | 50

Im doing this in JSP , because of the environment issue i doing this in the Notepad++ , anyway i just need the logic .Please Guide.

Upvotes: 0

Views: 33

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

In MySql there is no Pivot syntax, so can do this with conditional aggregation:

selec port,
      sum(case when type = 'd30' then size else 0 end) as d30,
      sum(case when type = 'd40' then size else 0 end) as d40,
      sum(case when type = 'd50' then size else 0 end) as d50
from table
group by port

If there is many ports then you can use group concat function. Look for MySql pivoting. For example MySQL pivot table

Upvotes: 1

Related Questions