How to put the result of the above query into the form below:

Shop_id image_url status image_url status
77901051 H: \ File \ 11111. JPG 0 H: \ File \ 2222 JPG 1



Thank you very much!!!!!!

CodePudding user response:

 
With t1 as 
(select a *, row_number () over (partition by shop_id order by sysdate) as an rn from table1) 
The select t1 shop_id, t1. Image_url, 0 as "status", tab1. Image_url, 1 as "status1" from t1 where rn=1 
Join (select * from t1 where rn=2) tab1 on t1, shop_id=tab1, shop_id 

CodePudding user response:

As with v
(select t. *, row_number () over (partition by t.s hopid order by t.s tatus) rn
The from t_shop t)
The select v. hopid,
Max (decode (v.r n, 1, v.i mage_url)) image_url_1,
'0' status_1,
Max (decode (v.r n, 2, v.i mage_url)) image_url_2,
'1' status_2
The from v
Group by v. hopid;

Specific pivot - 11 g function
As with v
(select shopid,
Image_url,
Row_number () over (partition by t.s hopid order by t.s tatus) rn
The from t_shop t)
The select v1 shopid,
V1. "1 _image_url",
'0' status_1,
V1. "2 _image_url,"
'1' status_2
Pivot from (select * from v (Max (v.i mage_url) as image_url for rn in (1, 2))) v1.