Transforming Table data into new view

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

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

edited May 23 '17 at 10:26

Community

1
1

answered Jun 11 '15 at 04:41

Giorgi Nakeuri

35,155
8
47
75

Thanks.. i solve it .. im using Switch ,but same logic. – Hakunamatata Jun 11 '15 at 06:31

Transforming Table data into new view

1 Answers1