CODEWITHSUNDEEP
sqljoindatatablesqlitestudio
FranB
FranB

Reputation: 11

SQLiteStudio: Is there a way to match row values from one table to column headers in another?

Say I have Table1 that lists products and the quantities of the products being purchased, but the table does not have the price of the products for the specified quantities:

ProductID ProductName Quantity Price
A1234 Shirt 25
B2345 Pants 100
C3456 Shoes 200

Then I have Table 2 that has a quantity column and other columns who's names match the row values of the ProductID column from Table1. The prices of these products are based on the quantity being purchased.

Quantity A1234 B2345 C3456
25 $500 $200 $150
50 $800 $400 $200
100 $900 $300 $230
150 $950 $350 $210

Is there anyway I can use both the Quantity column and the ProductID column in Table1 to pull prices from Table2 into Table1?

I would prefer a solution that does not use the Pivot function as I am not yet working in SQLServer and I do not think I can use Pivot in SQLiteStudio.

Upvotes: -2

Views: 100

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

This is achievable using left join

select t1.*, coalesce(t2.A1234, t3.B2345, t4.C3456) As Price from table1 t1
left join table2 t2 on t2.Quantity = t1.Quantity and t1.ProductID = 'A1234'
left join table2 t3 on t3.Quantity = t1.Quantity and t1.ProductID = 'B2345'
left join table2 t4 on t4.Quantity = t1.Quantity and t1.ProductID = 'C3456'

try this dbfiddle

Upvotes: 0

Related Questions