Link to home
Start Free TrialLog in
Avatar of Southern_Gentleman
Southern_GentlemanFlag for United States of America

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

Open in new window

Avatar of xiao jinshou
xiao jinshou

If I did this query, I would do it in a two steps logic.  First step is to save the results of your query into a temp table (#Sample), and second step to do the convert from null to blank.

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
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Southern_Gentleman

ASKER

Good stuff, thank you for taking the time.