CODEWITHSUNDEEP
sqloracle11goracle10g
Kate
Kate

Reputation: 285

repeat record in sql with respect to a column value

I'm looking any logic to get the out of this data into given below format.

But am not able to find any logic to achieve this in sql.

Given Table

ID                   Country         AreaDiscount        Email  
AB0215000001         Belgium         120 cm + 10%        [email protected]
                                     130 cm + 20%        [email protected]
                                     140 cm + 30%        [email protected]
AB0215000002         Spain           220 cm + 30%        [email protected]
                                     1420 cm + 30%       [email protected]
                                     1520 cm + 10%       [email protected]

Required

ID                   Country         AreaDiscount
AB0215000001         Belgium         120 cm + 10%
AB0215000001         Belgium         130 cm + 20%
AB0215000001         Belgium         140 cm + 30%                                                
AB0215000002         Spain           220 cm + 30%
AB0215000002         Spain           1420 cm + 30%
AB0215000002         Spain           1520 cm + 10%

Can I get any logic or suggestion to achieve this ?

Thanks !! :)

Upvotes: 0

Views: 49

Answers (2)

Recoil
Recoil

Reputation: 178

I'm fully agreed with Gordon Linoff.

You can try following approach, which is a little bit different than his answer :

SELECT NVL(ID, NVL(LAG(ID IGNORE NULLS) OVER(ORDER BY ROWID), ID)) AS ID,
       NVL(COUNTRY, NVL(LAG(COUNTRY IGNORE NULLS) OVER(ORDER BY ROWID), COUNTRY)) AS COUNTRY,
       AREADISCOUNT
FROM   TAB1 T
ORDER  BY ROWID;

But be careful. The use of the rowid for this matter ist not really "best practice" and you have to check the result in detail for correctness!

Update

With the additional column, you may this for grouping/ordering:

SELECT NVL(ID, NVL(LAG(ID IGNORE NULLS) OVER(ORDER BY EMAIL), ID)) AS ID,
       NVL(COUNTRY, NVL(LAG(COUNTRY IGNORE NULLS) OVER(ORDER BY EMAIL), COUNTRY)) AS COUNTRY,
       AREADISCOUNT,
       EMAIL
FROM   TAB1 T
ORDER  BY ID, AREADISCOUNT;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Assuming that you have a column that specifies the ordering, you can use lag(. . . ignore nulls):

select lag(id ignore nulls) over (order by orderingcol) as id,
       lag(country ignore nulls) over (order by orderingcol) as country,
       areadiscount
from t;

Upvotes: 1

Related Questions