PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL fetch values of goods and categories from one column and past them in two diferent

This is the table goodsandcat

Item Key
Electronics 0
Smartphones 1
Laptops 1
Cameras 1
Headphones 1
Clothing 0
T-shirts 1
Jeans 1
Dresses 1
Jackets 1

Column Item has names of categories and goods with their respective 0 and 1 in column Key.
It is required to fetch all categories and goods into two diferent columns like Goods and Categories. So, every goods-item must have its respective category-item in each row.

I tried to do this SQL

SELECT 
    CASE WHEN Key = 0 THEN Item ELSE NULL END AS Category,
    CASE WHEN Key = 1 THEN Item ELSE NULL END AS Goods
FROM goodsandcat;

The resul of the query was:

Category Goods
Electronics NULL
NULL Smartphones
NULL Laptops
NULL Cameras
NULL Headphones
Clothing NULL
NULL T-shirts
NULL Jeans
NULL Dresses
NULL Jackets

But it is expected to fill out by respective category-item all NULLs in column Category. So, it must be like this:

Category Goods
Electronics NULL
Electronics Smartphones
Electronics Laptops
Electronics Cameras
Electronics Headphones
Clothing NULL
Clothing T-shirts
Clothing Jeans
Clothing Dresses
Clothing Jackets

How to write sql-query fo getting above mentioned result?

Tags:

2

Answers


  1. 0

    There is no relation between the Electronics and goods ,Clothing and clothing items you can change Electronics to 0 and Electronics items to 1 similarly clothing to 2 and clothing items to 3 then change your sql query accodingly.

    Login or Signup to reply.
  2. 0

    Agreeing with the previous comment. This is not like a spreadsheet, where You can create a relationship based on the position within the sheet.

    Instead, if you have a key for each row, you may be able to find a query that will group them, then build your query.

    What I’ve done with things like this is drop it into a spreadsheet then create a query/view that will insert things into the DB, something like:

    Column A Contains the Items list, Column B contains the Keys then
    Column C contains this formula:
    =IF(B2=0,A2,C1)
    Column D contains this formula:
    =IF(B2=1,A2,"NULL")
    Then Column E contains this formulas:
    ="INSERT INTO Item_Table (Category, Goods) VALUES ('" & C2 & "', '" & D2 & "');"

    Your result in column E will be formulas you can paste into your DB to create the table you want.

    e.g.

    INSERT INTO goodsandcat (Category, Goods) VALUES ('Electronics', 'NULL');
INSERT INTO goodsandcat (Category, Goods) VALUES ('Electronics', 'Smartphones');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search