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?
Andrii is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.