Where to use ISNULL to convert NULL in PIVOT result set
-
Hi,
I can't figure out where to use ISNULL (or COALESCE) to convert NULL values in the pivot result set to 0. I need to do this because I will be using UNPIVOT to transform the data and it discards any rows that contained NULL.;WITH [Data] AS (
SELECT A.fldProductCode
,CASE(B.fldStockAuditCode)
WHEN 'NEW_STOCK_IN' THEN 'qty. rec''d.'
WHEN 'RETURN_IN' THEN 'returns'
WHEN 'STOCK_CONSUMED_AT_CHECKOUT' THEN 'despatched'
WHEN 'STOCK_DAMAGE_OUT' THEN 'damaged'
WHEN 'STOCK_DISCREPANCY_IN' THEN 'manual increase'
WHEN 'STOCK_DISCREPANCY_OUT' THEN 'manual decrease'
WHEN 'STOCK_WASTE_OUT' THEN 'scrapped'
ELSE B.fldStockAuditCode END AS [fldStockAuditCode]
,CAST(FLOOR(CAST(C.StartDate as FLOAT)) AS DATETIME) AS [FloorStartDate]
,SUM(COALESCE(D.fldStockAuditDelta,0)) AS [TotalDelta]
FROM tlkp_Product AS A CROSS JOIN
tlkp_Stock_Audit_Type AS B INNER JOIN
vw_ProductStockingPeriod AS C ON C.ProductID = A.fldProductID LEFT OUTER JOIN
vw_Stock_Quantity_Update_Activity AS D ON D.fldProductID = A.fldProductID AND
D.fldStockAuditID = B.fldStockAuditID
AND D.fldActivityDateTime >= C.StartDate
AND D.fldActivityDateTime < C.EndDate
WHERE (C.StartDate >= '2017-01-16' AND C.StartDate < '2017-06-27') OR
(C.EndDate >= '2017-01-16' AND C.EndDate < '2017-06-27') OR
(C.StartDate < '2017-01-16' AND C.EndDate > '2017-06-27')
GROUP BY A.fldProductCode
,B.fldStockAuditCode
,C.StartDate
,C.EndDate)
,[PivotResults] AS (
SELECT *
FROM [Data]
PIVOT
(
SUM(TotalDelta)
FOR fldProductCode IN ([prod1],[prod2],[prod3])
)
AS [Piv]
)select * from [PivotResults] order by floorstartdate, fldstockauditcode
-
I would put it in the SELECT clause of your [PivotResults] CTE query, instead of saying SELECT *, but it could also be in the final SELECT * FROM [PivotResults]
A question I have is what are you trying to achieve if you unpivot the data again? Is there something missing in the query in the [Data] CTE?
-
I wouldn't put it anywhere in a PIVOT for the very reason of NULLs. If you use a CROSSTAB instead, it's all "auto-magic" and can be twice as fast, as well. They also make the idea of doing row and grand totals a whole lot easier.
http://www.sqlservercentral.com/articles/T-SQL/63681/--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Chris Harshman - Wednesday, June 21, 2017 12:11 PM
Hi Chris, thank you for getting back to me. I'm sorry, but doesn't that mean I have to hard code each column product name? I only ask because the number of columns is dynamic so I didn't want to have to maintain the code each time a new product is added.
In answer to your question about me unpivoting a pivot - I am trying to replicate a stock report which is being created manually in Excel with a very unusual set of requirements. I can achieve exactly what I want with just the PIVOT alone, however I cannot use the results in an SSRS report using a table control because it contains a variable number of columns (i.e. if someone adds or removes a product from the product table) and if I use the pivot result set I need to hard code every product column, and if a product is added I need to update and redeploy the report.
I found out I can use a Matrix control, however the data needed to drive it needs to be in a different format, so I added the code to unpivot the data to get NULLS for a product when no values exist in a particular date range, the downside was that it ignores NULLS, so I wanted to add them back in. Screenshot of the report I am trying to mimic below (yellow highlighting be me). If you look at Product 3 (screenshot below), what the report is trying to convey, is that between 02/02/2017 and 27/04/2017, 399x of this product were booked in to stock, 165x were despatched and 234x were scrapped, but these values are only shown at the beginning of the date range, other rows are empty. Then on the 27/04/2017, a new version of the product was produced and stock was booked in/despatched/returned for all of period 5. I have tried to illustrate the date ranges that some stock figures apply to by highlighting vertical groups in yellow.
- Chris Harshman - Wednesday, June 21, 2017 12:11 PM
I have looked at this again and got it working by putting it in the final SELECT as you suggested - I had to build up a dynamic SQL statement to wrap the columns in COALESCE, but finally got it working, thank you.
- r.gall - Thursday, June 22, 2017 4:51 AM
Seriously... consider using the CROSSTAB method instead of that miserable PIVOT. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - r.gall - Thursday, June 22, 2017 2:13 AM
You need to hard code each column product name in the PIVOT clause of your query anyway wouldn't you? It is possible to use dynamic SQL, but then you'd have the column list dynamically built already and could just use it in both places.
I guess I'm still confused about pivoting and then unpivoting the data though, if all you want to do is feed data to an SSRS report, then maybe what you really need is an outer join from a calendar table of some sort to your stock activity tables? That would be more efficient than PIVOT or Jeff's CROSS TAB methodology. (BTW, Jeff is right, if you do have to turn rows into columns, then the CROSS TAB method can indeed perform better and be more flexible than PIVOT)
- Chris Harshman - Thursday, June 22, 2017 8:49 AM
Hi
I agree - and I have made a note to myself to revisit the crosstab method soon (just not now as I need to complete this report for management) - I did try it earlier but failed miserably.
I didn't want to go in to too much detail about the report as I thought it might be a bit off-topic. I think it's incredibly complex myself, and it's taken me days to get my head around what the report requirement is and how it relates to my database because there is no calendar to get the dates from unfortunately - it's not a simple case of "show me the product quantities and reasons between the start and end of the month (as much as I'd like it to be!). Instead, a product is added to the system initially (say on 1/1/17), and then at some point down the line the product changes slightly (say on 1/3/17). This means that all stock activities (despatching the product / wasting stock / marking stock as damaged / booking in returns) is now associated with the date range that it was initially booked in, and the new version coming in (between 1/1/17 and 1/3/17) and is summed within this period by the reason (despatched/damaged etc...). If the user then runs the stock report to show all products and their stock changes between/1/2/17 and 1/3/17, the report will expand the range to show all the way back to 1/1/17 because the stock period for that product intersects with the date range the user choose. Not sure if that will make sense to anyone other than me so sorry if it doesn't make sense!
- Chris Harshman - Thursday, June 22, 2017 8:49 AM
I don't know much about SSRS but the use of a "MATRIX" would be what someone wants to use for SSRS.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply