Filtering inside the PIVOT command

Transact-SQL
1

I'm trying to filter before I PIVOT. I only want the Fuel Surcharge from the ManifestRate table when the enddate is 12-31-9999. Problem is, the rest of the columns are in that table have enddates as well. Should I use a CASE statement or something? This is what I have so far:

SELECT * into #test1 FROM
(
select distinct m1.State, m1.Market, l.LabName, m.MnifestName, mr.ManifestId, r.RateName, mr.Value
from Manifests m
left join ManifestRate mr on [m.ID](http://m.id/) = mr.ManifestId
inner join Markets m1 on m.Marketid = [m1.ID](http://m1.id/)
inner join labs l on l.labid = m.LabId
left join RateType r on mr.RateTypeId = r.RateTypeId
where m.IsActive = 1
and mr.Enddate = '12-31-9999'
) tp
PIVOT
(
max(Value)
FOR [RateName] in ([Fuel Surcharge %],[Supply Cost],[Linehaul Billing],[Linehaul Cost],[Dry Ice Billing],[Dry Ice Cost],[Supply Charge],[COB])
) p
2

I'm not sure what you are expecting, but you will get all the columns in the pivot whether they have data or not. If you are looking to only provide columns that have an enddate of 12/31/9999, then you'll need to do it dynamically.

Drop table if exists #ManifestRate 
go

create table #ManifestRate (
	ManifestName nvarchar(20),
	ManifestRateName nvarchar(20),
	ManifestRate numeric(10,2),
	EndDate date)

insert into #ManifestRate values 
('m1','Fuel Surcharge %', 1.1, '12/31/9999'),
('m2','Fuel Surcharge %', 2.1, '12/31/2022'),
('m3','Fuel Surcharge %', 3.1, '12/31/2023'),
('m4','Fuel Surcharge %', 4.1, '12/31/9999'),
('m1','Supply Cost', 1.2, '12/31/2022'),
('m2','Supply Cost', 2.2, '12/31/2022'),
('m3','Supply Cost', 3.2, '12/31/9999'),
('m4','Supply Cost', 4.2, '12/31/2022')

--Returns all columns regardless as to whether they have end date of 12/31/9999 or not
select * from (
select  ManifestName, ManifestRateName, ManifestRate
from #ManifestRate m
where Enddate = '12-31-9999'
) tp
PIVOT
(
max(ManifestRate)
FOR ManifestRateName in ([Fuel Surcharge %],[Supply Cost],[Linehaul Billing],[Linehaul Cost],[Dry Ice Billing],[Dry Ice Cost],[Supply Charge],[COB])
) p

-- show only columns with 12/31/9999 end date

declare @Colslist nvarchar(max) ,
		@SQL nvarchar(max)

DECLARE @Cols TABLE (Head VARCHAR(MAX))  

  
INSERT @Cols (Head)  
SELECT DISTINCT ManifestRateName  
FROM #ManifestRate t  
WHERE Enddate = '12-31-9999'


SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
set @SQL = 'select * from (
select  ManifestName, ManifestRateName, ManifestRate
from #ManifestRate m
where Enddate = ''12-31-9999''
) tp
PIVOT
(
max(ManifestRate)
FOR ManifestRateName in (' + @ColsList + ')) PVT'

print @SQL
exec(@SQL)
3

I think you need to move the date check into the LEFT JOIN.


SELECT * into #test1 FROM
(
select distinct m1.State, m1.Market, l.LabName, m.MnifestName, mr.ManifestId, r.RateName, mr.Value
from Manifests m
left join ManifestRate mr on [m.ID](http://m.id/) = mr.ManifestId 
    and mr.Enddate = '12-31-9999' --<<--<<--
inner join Markets m1 on m.Marketid = [m1.ID](http://m1.id/)
inner join labs l on l.labid = m.LabId
left join RateType r on mr.RateTypeId = r.RateTypeId
where m.IsActive = 1
) tp
PIVOT
...
1 Like