rows data to columns

[paljnad]

I have the following query:

select a.anumber,
(case
when b.bu_code = 'DIST' and b.req_flag = 'R' then
'TRUE'
else
' '
end) as DIST,
(case
when b.bu_code = 'BCTR' and b.req_flag = 'R' then
'TRUE'
else
' '
end) as BCTR,
(case
when b.bu_code = 'STATE' and b.req_flag = 'R' then
'TRUE'
else
' '
end) as STATE
from account a, account_bu_type b
where a.company_code = b.company_code(+)
and a.number = b.anumber(+)

which gives me results like this:
ANUMBER DIST BCTR STATE
62125 TRUE
62125 TRUE

How do I make the output look like:
ANUMBER DIST BCTR STATE
62125 TRUE TRUE

I tried using a group function but I dont know what aggregation function to use.

Thanks.

 

[SantaMufasa]

Paljnad,

Rather than re-code a solution here, can I suggest that you refere to the thread, "thread1177-1511701". It should show a solution that it just what you need.

Let us know if that is true.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[paljnad]

Thanks, SantaMufasa. I tried it but for some reason, it puts duplicates out there. Also, how do I join in another table. The query I have now is:

select anumber,
ltrim(substr(max(sys_connect_by_path(bu_code, ', ')), 2)) BU_CODE
from (select anumber,
bu_code,
row_number() over(partition by anumber order by bu_code) rn
from account_bu_type where req_flag = 'R')
start with rn = 1
connect by prior rn = rn - 1
and prior anumber = anumber
group by anumber
order by anumber

and the o/p looks like

1110621 LOB
1111 LOB , SAD
1111000 LOB
11110000 LOB
11110401 LOB , LOB
11110601 LOB , LOB
11110621 LOB , LOB
11120601 LOB , LOB
11145000 LOB , LOB
41105000 BCTR , BCTR , LOB , LOB , OPC , PROD , REICH, REICO, TAXST

 

[Beilstwh]

i dont see any duplicate anumbers in your output.

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

Bill,

I believe that the duplicates to which Paljnad refers are the "LOB, LOB" entries.

I'm sorry that I haven't had a chance to troubleshoot the code, and I'm under the gun to complete a database build, so it may be later tonight before I can pay attention to this.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

Have you considered using COLLECT instead ? There are plenty of examples of using this in the manuals or elsewhere on the internet.

 

[Dagon]

As to why you're getting duplicates, that could just be that your data is not unique. Try changing it to:

select anumber,
       ltrim(substr(max(sys_connect_by_path(bu_code, ', ')), 2)) BU_CODE
  from (select anumber,
               bu_code,
               row_number() over(partition by anumber order by bu_code) rn
          from (select distinct anumber, bu_code from account_bu_type where req_flag = 'R'))
 start with rn = 1
connect by prior rn = rn - 1
       and prior anumber = anumber
 group by anumber
 order by anumber