I have three tables in below format:
Table_1 (Tax Types)
TaxTypeID Code Description LocationID
1 TaxA DescA 1
2 TaxB DescB 1
3 TaxC DescB 1
Table_2 (Tax Plans)
PlanID Name Description LocationID
1 Plan1 Plan1 1
Table_3 (Cross Reference)
TaxTypeID PlanID TaxPercentage
1 1 5.00
2 1 7.00
So I need to write a query which outputs below result when Plan_Id is passed and also by matching Table 1 and 2 LocationID:
PlanID Name TaxA TaxB TaxC
1 Plan1 5.00 7.00 0.00
If table_3 does not have cross reference between table_1 and table_2 percentage should be displayed as 0.00. Mainly I need to display rows in Table_1(tax types) as column names in my output and this should be dynamic, based on rows in Table_1(tax types). So, if a new tax type row "TaxD" is added, out should have a new column TaxD with 0.00 as percentage.
I know by using Pivot in SQL,we can get row values as column names and I tried below query
select *
from
(
select TaxTypeId,Code from Table_1 ) as p
pivot
(
max(Code)
for Code in ( [TaxA],[TaxB],[TaxC])
)as pvt
But it is not close to what I need.