How to display row values โโas column names?
I have three tables in the following 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 that outputs below result when passing Plan_Id and also by mapping tables 1 and 2 LocationID:
PlanID Name TaxA TaxB TaxC
1 Plan1 5.00 7.00 0.00
If table_3 is not cross-referenced between tables_1 and table_2, then the percentage should be displayed as 0.00. Basically I need to display rows in table_1 (tax types) as column names in my release, and that should be dynamic based on rows in table_1 (tax types). So, if a new line of tax type "TaxD" is added, it should have a new column TaxD with 0.00 as a percentage.
I know using Pivot in SQL we can get row values โโas column names and I tried to execute query
select *
from
(
select TaxTypeId,Code from Table_1 ) as p
pivot
(
max(Code)
for Code in ( [TaxA],[TaxB],[TaxC])
)as pvt
But this is not close to what I need.
source to share
You need to use max(TaxPercentage)
instead max(code)
in pivot
select * from
(
SELECT c.planid,c.NAME,taxpercentage,a.Code
FROM table_1 a
JOIN table_3 b
ON a.taxtypeid = b.taxtypeid
JOIN table_2 c
ON c.planid = b.planid
) as p
pivot
(
max(TaxPercentage)
for Code in ( [TaxA],[TaxB],[TaxC])
)as pvt
If the values โโare not defined in the code, you need to use dynamic pivot
declare @sql varchar(8000),@col_list varchar(8000),@pivot_list varchar(8000)
set @col_list =stuff((select distinct ',Coalesce('+ quotename(Code) + ',0) as '+ quotename(Code) from Table_1 for xml path('')),1,1,'')
set @pivot_list =stuff((select distinct ','+quotename(Code) from Table_1 for xml path('')),1,1,'')
set @sql = 'select * from
(
SELECT c.planid,c.NAME,taxpercentage,a.Code
FROM table_1 a
JOIN table_3 b
ON a.taxtypeid = b.taxtypeid
JOIN table_2 c
ON c.planid = b.planid
) as p
pivot
(
max(TaxPercentage)
for Code in ( '+@pivot_list+')
)as pvt'
--print @sql
exec(@sql)
source to share