Andrew Andrew - 6 months ago 30
SQL Question

Common multiple rows in single row in oracle sql

I have below query where instead of creating multiple entries(rows) for the same BN it should create entry in same row with FC,FM,MS value.

select bspt.BN, FC, FM, MS from(select distinct(ozt.BRD_ID),
bp.NAME as BN,
os.OSS FC, '' FM, '' MS
from PR_BP ozt, PR_BP_BPP bp, BDD b, OS_VIEW os
where bp.BRD_ID = ozt.BRD_ID AND ozt.BRD_ID = os.OBJECT_ID and
os.OST = 'PR_BP_BPP' AND (ozt.BID = 10001)
and ozt.BID = b.BID AND ozt.BP_ACTIVE = 'Y'
UNION ALL
select distinct(ozt.BRD_ID),
bp.NAME as BN,
'' FC, os.OST FM, '' MS
from PR_BP ozt, PR_BP_BPP bp, BRAND b, OS_VIEW os
where bp.BRD_ID = ozt.BRD_ID AND ozt.BRD_ID = os.OBJECT_ID and
os.OST = 'PR_BP_BPP' AND (ozt.BID = 10002)
and ozt.BID = b.BID AND ozt.BP_ACTIVE = 'Y'


And the above query is generating output with multiple entries for BNAME column. So instead of creating separate entry,it should create only single row for common BNAME.

vkp vkp
Answer

All of this can be done with one query with conditional aggregation.

select bp.NAME as BIZNAME, 
max(case when bbp.BRAND_ID = 10001 then os.OBJECT_STATUS end) FC,
max(case when bbp.BRAND_ID = 10002 then os.OBJECT_STATUS end) FM, 
max(case when bbp.BRAND_ID = 10007 then os.OBJECT_STATUS end) MS 
from PR_BP bbp
JOIN PR_BP_BPP bp ON bp.BP_ID = bbp.BP_ID
JOIN BRAND b ON bbp.BRAND_ID = b.BRAND_ID
JOIN OS_VIEW os ON bbp.BP_ID = os.OBJECT_ID
where os.OBJECT_TYPE = 'PR_BP_BPP' AND bbp.BP_ACTIVE = 'Y'
group by bp.NAME