CODEWITHSUNDEEP
sqlpivotisnull
Tim Richards
Tim Richards

Reputation: 324

ISNULL syntax in SQL pivot

I have the following bit of code

SELECT @columns = COALESCE(@columns + ', ','') + QUOTENAME(PERIOD)
FROM (SELECT DISTINCT PERIOD FROM @p_l_summary) AS b
ORDER BY b.PERIOD

which works perfectly but generates some NULL values.

I know that I need to wrap an ISNULL around the @columns but can anyone give me the right syntax - whatever I try either generates an error or seems to have no effect.

Upvotes: 0

Views: 227

Answers (2)

Alexey A.
Alexey A.

Reputation: 902

COALESCE returns the first non-null value, so it's acting as ISNULL in your case. So your query can return null only if PERIOD column is null. You can either filter such values out: 

SELECT @columns = COALESCE(@columns + ', ','') + QUOTENAME(PERIOD)
FROM (SELECT DISTINCT PERIOD FROM @p_l_summary WHERE PERIOD IS NOT NULL) AS b
ORDER BY b.PERIOD

or use ISNULL on PERIOD:

SELECT @columns = COALESCE(@columns + ', ','') + ISNULL(QUOTENAME(PERIOD), '')
FROM (SELECT DISTINCT PERIOD FROM @p_l_summary) AS b
ORDER BY b.PERIOD

Upvotes: 2

John Woo
John Woo

Reputation: 263693

how about adding additional condition?

WHERE PERIOD IS NOT NULL

Upvotes: 1

Related Questions