I have oracle database table 'App' with few sample rows as following-
ID NAME SN
123 TV 032361097
123 CB G92970256
236 TV 050791069
345 CB 45681089
456 TV
456 CB
I want to get output in following format by Transposing Rows into Columns -
ID NAME_TV SR_TV NAME_CB SR_CB
123 TV 032361097 CB G92970256
236 TV 050791069
345 CB 45681089
456 TV CB
It can be done with following query if there are only two valid values for name.
WITH
tv as (select id, name, sn from app where name = 'TV'),
cb as (select id, name, sn from app where name = 'CB')
SELECT
nvl(tv.id, cb.id) AS id,
tv.name AS name_tv,
tv.sn AS sn_tv,
cb.name AS name_cb,
cb.sn AS sn_cb
FROM tv
FULL OUTER JOIN cb
ON tv.id = cb.id;
For more values use more tricky PIVOT:
SELECT * FROM (
SELECT id, name, sn
FROM app
) PIVOT (
MAX(name) as name, MAX(sn) as sn
FOR name IN ('TV' as TV, 'CB' as CB) --put other name values there
)
You can use conditional aggregation:
select id, 'TV' as name_tv, sum(case when name = 'TV' then sn end) as sn_tv,
'CB' as name_cb, sum(case when name = 'CB' then sn end) as sn_cb
from app
group by id;
I do have a question: Why do you have the name_tv and name_cb columns? They seem redundant.
You could join the table on itself.
select tv.id,
tv.name as name_tv,
tv.sn as sr_tv,
cb.name as name_cb,
cb.sn as sr_cb
from app tv
join app cb
on tv.id = cb.id
where tv.name = 'TV'
and cb.name = 'CB'
