Southern_Gentleman
asked on
Pivot blank space for null
I'm trying to put a blank or empty space instead of a null in my pivot. I for some reason am having trouble with this since it has a dense_rank Over() in the case, which is where im trying to put my isnull
)
SELECT *
FROM
(
SELECT '12/31/2017' AS StartDate, '11:59' AS StartTime, '' AS Enddate, '' AS EndTime, a.baseid,'Ground Services' AS ProductCategory, a.productCode, a.productName,concessiontype, ISNULL(CAST(taxRate AS VARCHAR(10)),'') AS taxrate,
CASE WHEN DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) = 1 THEN 'IncludeA'
WHEN DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) = 2 THEN 'IncludeB'
WHEN DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) = 3 THEN 'IncludeC'
WHEN DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) = 4 THEN 'IncludeD'
WHEN DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) = 4 THEN 'IncludeE'
ELSE ISNULL(CAST(a.taxRate AS VARCHAR(10)),'') END AS Taxrank
FROM concessions AS a
JOIN [SQLRM].[SFSRMSandbox].[dbo].[PEGABases] AS b ON b.baseid = a.baseId
JOIN dbo.ProductCodes AS c ON c.productCode = a.productCode
LEFT OUTER JOIN [SQLRM].[SFSRMSandbox].[dbo].[NonfuelCPAtoPEGABridge] AS d ON d.productCode = a.productCode
WHERE c.fuelAssoc IS NULL AND a.taxRate <> 0
) AS concessionController
PIVOT
(
MAX(taxrate)
FOR Taxrank IN
([IncludeA],[IncludeB],[IncludeC],[IncludeD],[IncludeE])
)
AS pivotable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good stuff, thank you for taking the time.
create table #sample (id int, col1 varchar(200),col2 varchar(200))
insert into #sample values
(1,'a1','b1')
,(2,'a2','b2')
,(5,'a5','b5')
,(6,null,'b5')
select
id
,isnull(col1,'')
,isnull(col2,'')
from #sample
- Xiao
drop table #sample