CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2460074
user2460074

Reputation: 1272

repeat a value of a row in a new column

I've this table with the following data

id    Product     Price 
1      ELECTRO     TV
2      null       null
3      null       null
4      Samsung    1000
5      LG         2000
6      Philips    1300
7      ELECTRO    Mobile
8      null       null
9      null       null
10     Samsung    1000
11     Nokia      2000
12     Sony       1300

I need to add another column and repeat the value the resultant table should be as below:

    id    Product     Price     Category
    1      ELECTRO     TV         TV
    2      null       null        TV
    3      null       null        TV
    4      Samsung    1000        TV
    5      LG         2000        TV
    6      Philips    1300        TV
    7      ELECTRO    Mobile      Mobile
    8      null       null        Mobile
    9      null       null        Mobile
    10     Samsung    1000        Mobile
    11     Nokia      2000        Mobile
    12     Sony       1300        Mobile

Can someone please help me out with this query? because i don't have any idea how can i do it

Upvotes: 2

Views: 2417

Answers (4)

Thomas Steven
Thomas Steven

Reputation: 449

There appear to be a number of things wrong with the design of your data here, and I really think that you need to address them before trying to make your query. For starters, it looks like Electro is a category, and should therefore be a column, but Product is already doing the work, which makes Electro in any form look redundant. If Electro is somehow not redundant, it should have it's own column.

Your rows containing null are also a symptom that something is almost certainly wrong with your data design, as it would seem unlikely that you have a TV with both no manufacturer and no price.

Additionally you appear to have mixed numeric and character data in the Price column: again suggesting that there is a serious flaw in the data design.

I don't think that you can really get the outcome you want to achieve given the data you appear to have, and to me it looks as if your underlying data needs to be redesigned.

Upvotes: 2

Husqvik
Husqvik

Reputation: 5809

WITH src AS (
SELECT 1 ID  , 'ELECTRO' Product, 'TV' Price FROM DUAL UNION ALL
SELECT 2     , null      , null  FROM DUAL UNION ALL
SELECT 3     , null      , null  FROM DUAL UNION ALL
SELECT 4     , 'Samsung'   , '1000'  FROM DUAL UNION ALL
SELECT 5     , 'LG'        , '2000'  FROM DUAL UNION ALL
SELECT 6     , 'Philips'   , '1300'  FROM DUAL UNION ALL
SELECT 7     , 'ELECTRO'   , 'Mobile'  FROM DUAL UNION ALL
SELECT 8     , null      , null  FROM DUAL UNION ALL
SELECT 9     , null      , null  FROM DUAL UNION ALL
SELECT 10    , 'Samsung'   , '1000'  FROM DUAL UNION ALL
SELECT 11    , 'Nokia'     , '2000'  FROM DUAL UNION ALL
SELECT 12    , 'Sony'      , '1300'  FROM DUAL)
SELECT
    src.*,
    LAST_VALUE(NVL2(TRIM(TRANSLATE(price, '0123456789', ' ')), price, null)) IGNORE NULLS OVER (ORDER BY ID) category
FROM
    src;

But agreed that your data structure doesn't look good.

Upvotes: 0

MT0
MT0

Reputation: 167877

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (id, Product, Price ) AS
          SELECT 1,  'ELECTRO', 'TV'     FROM DUAL
UNION ALL SELECT 2,  null,      null     FROM DUAL
UNION ALL SELECT 3,  null,      null     FROM DUAL
UNION ALL SELECT 4,  'Samsung', '1000'   FROM DUAL
UNION ALL SELECT 5,  'LG',      '2000'   FROM DUAL
UNION ALL SELECT 6,  'Philips', '1300'   FROM DUAL
UNION ALL SELECT 7,  'ELECTRO', 'Mobile' FROM DUAL
UNION ALL SELECT 8,  null,      null     FROM DUAL
UNION ALL SELECT 9,  null,      null     FROM DUAL
UNION ALL SELECT 10, 'Samsung', '1000'   FROM DUAL
UNION ALL SELECT 11, 'Nokia',   '2000'   FROM DUAL
UNION ALL SELECT 12, 'Sony',    '1300'   FROM DUAL

Query 1:

SELECT ID,
       PRODUCT,
       PRICE,
       CASE PRODUCT
         WHEN 'ELECTRO' THEN PRICE
         ELSE LAG( CASE PRODUCT WHEN 'ELECTRO' THEN PRICE END ) IGNORE NULLS OVER ( ORDER BY ID )
         END AS CATEGORY
FROM   test

Results:

| ID | PRODUCT |  PRICE | CATEGORY |
|----|---------|--------|----------|
|  1 | ELECTRO |     TV |       TV |
|  2 |  (null) | (null) |       TV |
|  3 |  (null) | (null) |       TV |
|  4 | Samsung |   1000 |       TV |
|  5 |      LG |   2000 |       TV |
|  6 | Philips |   1300 |       TV |
|  7 | ELECTRO | Mobile |   Mobile |
|  8 |  (null) | (null) |   Mobile |
|  9 |  (null) | (null) |   Mobile |
| 10 | Samsung |   1000 |   Mobile |
| 11 |   Nokia |   2000 |   Mobile |
| 12 |    Sony |   1300 |   Mobile |

Upvotes: 1

Mirza
Mirza

Reputation: 213

How do you know what value to assign to the each row (either TV or Mobile)?

But avoiding that "little" detail, you first need to add a column with an ALTER statement:

ALTER TABLE yourTVAndMobileData
ADD Category varchar2(15);

And then update all of the rows with UPDATE statement:

UPDATE yourTVAndMobileData
SET Category = DECODE ( id, ( SELECT m.id 
                                  FROM Mobiles m 
                                  WHERE m.id=id; ), "Mobile",
                            "TV" );

I have assumed you have a table Mobiles with a column "id" which is a foreign key for your table of all devices. Change this logic as it suits you.

I think this should work. Let me know if it doesn't...

Upvotes: 0

Related Questions