How to display row values as column names?

Viewed 252

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.

1 Answers
Related