CODEWITHSUNDEEP
mysqlrowtranspose
sharvanaz
sharvanaz

Reputation: 85

How to transpose row into column

I have a table which has huge value like below. 'fbb' type value will be added later in the SERVICE column of the table. 

SITE_NAME  SERVICE  BANDWIDTH
MBCGP1     3g       30
BONWRT     3g       40
BORNR1     3g       30
MBCGP1     2g       8
BONWRT     2g       4
BORNR1     2g       8
BOSBB1     2g       8

I want to insert this values in a table like this.

SITE_NAME  2g_bw  3g_bw  fbb_bw
MBCGP1     8       30     0
BONWRT     4       40     0
BORNR1     8       30     0
BOSBB1     8       0      0

I do not have any idea how to do it in writing one sql.

Upvotes: 0

Views: 76

Answers (1)

user6307642
user6307642

Reputation:

You can use a instance of the table for 2g, and another for 3g, and then join these tables.

select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
join yourtable 3g
  on 3g.site_name = 2g.site_name
 and 3g.service = '3g'
where 2g.service = '2g'

Then, you can insert the results in another table with INSERT ... SELECT

insert into othertable (site_name, 2g_bw, 3g_bw, fbb_bw)
select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
join yourtable 3g
  on 3g.site_name = 2g.site_name
 and 3g.service = '3g'
where 2g.service = '2g'

Update

As @Strawberry propose, you can use a full join to get nulls from 2g or 3g (not both).

select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
left outer join yourtable 3g
   on 3g.site_name = 2g.site_name
  and 3g.service = '3g'
where 2g.service = '2g'
union
select 3g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 3g
left outer join yourtable 2g
   on 2g.site_name = 3g.site_name
  and 2g.service = '2g'
where 3g.service = '3g'

The first request get results with 2G and 3G associated, the second results with 3G and 2G associated. If you make union of that results, on are sure to retrieve all results.

Upvotes: 2

Related Questions