How to use GROUPING function in SQL common table expression - CTE

Question

I have the below T-SQL CTE code where i'm trying to do some row grouping on four columns i.e Product, ItemClassification, Name & Number.

;WITH CTE_FieldData
AS (
    SELECT 
      CASE(GROUPING(M.CodeName))
            WHEN 0 THEN M.CodeName
            WHEN 1 THEN 'Total'
            END AS Product,

        CASE(GROUPING(KK.ItemClassification))
            WHEN 0 THEN KK.[ItemClassification]
            WHEN 1 THEN 'N/A'
            END AS [ItemClassification],

        CASE(GROUPING(C.[Name]))
            WHEN 0 THEN ''
            WHEN 1 THEN 'Category - '+ '('+ItemClassification+')'
            END AS [Name],

            CASE(GROUPING(PYO.Number))
            WHEN 0 THEN PYO.Number
            WHEN 1 THEN '0'
            END AS [Number],

        ISNULL(C.[Name],'') AS ItemCode,
        MAX(ISNULL(PYO.Unit, '')) AS Unit,
        MAX(ISNULL(BT.TypeName, '')) AS [Water Type],
        MAX(ISNULL(PYO.OrderTime, '')) AS OrderTime,
        MAX(ISNULL(BUA.Event, '')) AS Event,
        MAX(ISNULL(PYO.Remarks, '')) AS Remarks,
        GROUPING(M.CodeName) AS ProductGrouping,
        GROUPING(KK.ItemClassification) AS CategoryGrouping,
        GROUPING(C.[Name]) AS ItemGrouping
    FROM CTable C INNER JOIN CTableProducts CM ON C.Id = CM.Id
                INNER JOIN MyData R ON R.PId = CM.PId 
                INNER JOIN MyDataDetails PYO ON PYO.CId = C.CId AND PYO.ReportId = R.ReportId
                INNER JOIN ItemCategory KK ON C.KId = KK.KId
                INNER JOIN Product M ON R.ProductId = M.ProductId
                INNER JOIN WaterType BT ON PYO.WId = BT.WId
                INNER JOIN WaterUnit BUA ON PYO.WUId = BUA.WUId
                WHERE R.ReportId = 4360
    GROUP BY M.CodeName, KK.ItemClassification, C.Name, PYO.Number
    WITH ROLLUP
)
SELECT 
    Product, 
    [Name] AS Category,
    Number, 
    Unit as ItemCode, 
    [Water Type], 
    OrderTime, 
    [Event], 
    [Comment]
    FROM CTE_FieldData

Below are the issues/problems with the data being returned by the script above and they are the ones i'm trying to fix.

1. At the end of each ItemClassification grouping, i extra record is being added yet it does not exist in the table. (See line number 4 & 10 in the sample query results screenshot attached).

2. I want the ItemClassification grouping in column 2 to be at the beginning of the group not at the end of the group. That way, ItemClassification "Category- (One)" would be at line 1 not the current line 5. Also ItemClassification "Category- (Two)" would be at line 5 not the current line 11

3. Where the "ItemClassification" is displaying i would like to have columns (Number, ItemCode, [Water Type], [OrderTime], [Event], [Comment]) display null. In the attached sample query results screenshot, those would be rows 11 & 5

4. The last row (13) is also unwanted.

I'm trying to understand SQL CTE and the GROUPING function but i'm not getting things right.

Answer 1

It looks like this is mostly caused by WITH ROLLUP and GROUPING. ROLLUP allows you to make essentially a sum line for your groupings. When you have WITH ROLLUP, it will give you NULL values for all of your non-aggregated fields in your select statement. You use GROUPING() in conjunction with ROLLUP to then label those NULL's as 'Total' or '0' or 'Category' as your query does.
1) Caused by GROUPING and ROLLUP. Take away both and this should be resolved.

2) Not sure what determines your groups and what would be defined as beginning or end. Order BY should suffice

3) Use ISNULL or CASE WHEN. If the Item Classification has a non null or non blank value, NULL each field out.

4) Take off WITH ROLLUP.