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?
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');