CODEWITHSUNDEEP
sqloracle-database
tinygeek
tinygeek

Reputation: 3

Group rows in Oracle using PARTITION BY

I have below 2 tables which i want to display like shown result

Data Table 1

id    col1      col2
t1    data1     data2
t2    data3     data4

Data Table 2

idOfTable1   col3       col4
t1           data5      data6
t1           data7      data8
t1           data9      data10    
t2           data11     data12

Expected result Want to display the id value in first row only for the common rows Result DataTable after Join of 2 tables on id column How can i achieve in Oracle

idofTable1      col1       col2       col3        col4
t1              data1      data2      data5       data6
                                      data7       data8
                                      data9       data10
t2              data3      data4      data11      data12

Upvotes: 0

Views: 79

Answers (2)

user11990287
user11990287

Reputation: 1

WITH a AS
 (SELECT t2.*,
         row_number() over(PARTITION BY idoftable1 ORDER BY col3) r2
    FROM t2),
b AS
 (SELECT t1.*,
         row_number() over(PARTITION BY id ORDER BY id) r1
    FROM t1)
SELECT id,
       col1,
       col2,
       col3,
       col4
  FROM b
 RIGHT JOIN a
    ON a.idoftable1 = b.id
   AND a.r2 = b.r1
 ORDER BY a.idoftable1,
          a.col3

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try below query - 

SELECT CASE WHEN LAG(T1.ID) OVER(PARTITION BY T1.ID ORDER BY T2.COL3) = T1.ID 
            THEN NULL
       ELSE T1.ID END AS ID,
       CASE WHEN LAG(T1.ID) OVER(PARTITION BY T1.ID ORDER BY T2.COL3) = T1.ID
       THEN NULL
       ELSE T1.COL1 END AS COL1,
       CASE WHEN LAG(T1.ID) OVER(PARTITION BY T1.ID ORDER BY T2.COL3) = T1.ID
       THEN NULL
       ELSE T1.COL2 END AS COL2,
       T2.COL3,
       T2.COL4
FROM T1
JOIN T2 ON T1.id = T2.idOfTable1
ORDER BY T1.ID, T2.COL3

Here is the fiddle.

Upvotes: 1

Related Questions