SQL query

id    sup_no   prim_flag

1         111          Y

2          111

3          222         Y

4          222

5          222

6          333         Y

create table temp_tbl

(

   id                        number,

   sup_no                    number ,

   prim_flag                  varchar2(1)

)

/

I have the above existing table and data and now i want the query to get data as below (55 is defaulted value)

BATCH  SUP_NO  PRIM_ID   SEC_ID

55            111            1                2

55             222           3                4

55             222           3                5

55             333           6