Learn From - Interactive SQL

You can use INNER JOIN and Case statement to split a column into two.

Below is shown the "Product" table:


Below is shown the "ProductDeliveryTBL" table:

SQL using INNER JOIN and Case statement

In the "Product" table, different products are listed. In "ProductDeliveryTBL" table, delivery status and the buyer names are listed.

Here in the "ProductDeliveryTBL" table, the column DeliveryStatus contains only two values "Delivered" and "Not Delivered". Using the Case statement we can split the DeliveryStatus column into "Delivered" and "Not Delivered" columns.


Note: The DeliveryStatus column is now split as "Delivered_Count" and "NotDelivered_Count" columns.

Above Query Executed Result:


In the SQL Query shown below the above query is used as a table with alias "tbl" in the From clause.

We can use this SQL Query shown below to know the "Delivered" and "NotDelivered" status of the products from the "ProductDeliveryTBL" table.

Text Editor below has the SQL Query, click the "View Result" button, to see the result.


Note: The From clause uses the SUBQUERY that acts as a table with alias name "tbl". In the SUBQUERY, Case Statements are used to seperate the "Delivered" and "NotDelivered" Products.
Inner Join is used to join both the tables using Primary Key ProductID column from the "Product" Table and Foreign Key ProductID column from the "ProductDeliveryTBL" table. Finally GROUP BY clause is used to get the sum of "Delivered" and "NotDelivered" Products.

SQL in Text Editor:

Select tbl.ProductID, P.ProductDesc, SUM(tbl.Delivered_Count) as Delivered, SUM(tbl.NotDelivered_Count) as NotDelivered From ( Select ProductID, (Case when DeliveryStatus = 'Delivered' then 1 else 0 end) as Delivered_Count, (Case when DeliveryStatus = 'NotDelivered' then 1 else 0 end) as NotDelivered_Count From ProductDeliveryTBL ) as tbl INNER JOIN Product as P on tbl.ProductID = P.ProductID GROUP BY tbl.ProductID, P.ProductDesc ORDER BY tbl.ProductID;