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

Southern_GentlemanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xiao jinshouCommented:
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
0
Mark WillsTopic AdvisorCommented:
The problem is in your SELECT * FROM ( <data source> PIVOT <aggregation for columns> )

You have to replace the SELECT *

Now, I havent tested, but, would look something like :
SELECT StartDate,StartTime,EndDate,EndTime,BaseID,ProductCategory
       ,ProductCode,ProductName,ConcessionType
       ,isnull([IncludeA],'') as IncludeA
       ,isnull([IncludeB],'') as IncludeB
       ,isnull([IncludeC],'') as IncludeC
       ,isnull([IncludeD],'') as IncludeD
       ,isnull([IncludeE],'') as IncludeE
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

Noticed that you have the same dense_rank definition for IncludeD and IncludeE and put a couple of question marks above.

Because the other method is to do the dense_rank and leave it as a number and then you could do :
SELECT StartDate,StartTime,EndDate,EndTime,BaseID,ProductCategory
       ,ProductCode,ProductName,ConcessionType
       ,isnull([1],'') as IncludeA
       ,isnull([2],'') as IncludeB
       ,isnull([3],'') as IncludeC
       ,isnull([4],'') as IncludeD
       ,isnull([5],'') as IncludeE
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, 
DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate)  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 
	([1],[2],[3],[4],[5)
)
AS pivotable

Open in new window


I have written an Article about Pivot - it does get into dynamic SQL but also discusses PIVOT in general: https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Southern_GentlemanAuthor Commented:
Good stuff, thank you for taking the time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.