COUNT and ROLLUP SUM of COUNT-CodePudding

I have below data in table

SECTIONNO   SIZENAME
1           M
1           2XL
1           XL
1           M
2           M
2           XL
2           2XL
2           M
3           L
3           2XL
3           XL
3           L
4           M
4           2XL
4           XL
4           L
5           L
5           XL
5           2XL
5           M

Result required

[SECTIONNO] wise count of [SIZENAME] sum of previous [SECTIONNO] count

SECTIONNO   M   L   XL  2XL
1           2   0   1   1
2           4   0   2   2
3           4   2   3   3
4           5   3   4   4
5           6   4   5   5

I tried PIVOT method, but it gives only SECTIONNO count only, unable to get COUNT total carry over to next SECTIONNO

SELECT *
FROM (
    SELECT SECTIONNO, SIZENAME
    FROM TableNAME
) s 
PIVOT (
    COUNT(SIZENAME) FOR [SIZENAME] IN ([M], [L], [XL], [2XL])
) PVT 
ORDER BY PVT.SECTIONNO

CodePudding user response：

Maybe you can try this

; with CTE AS 
(
    SELECT * FROM 
    ( 
        SELECT 
            LAYNO, 
            SECTIONNO, 
            SIZENAME 
        FROM TableNAME 
    ) s 
    PIVOT
    ( 
        COUNT(SIZENAME) 
        FOR [SIZENAME] 
            IN 
                ([M], [L], [XL], [2XL])
    ) PVT 
) 

SELECT 
    C1.LAYNO, 
    C1.SECTIONNO,
    SUM(C2.M) M,
    SUM(C2.L) L,
    SUM(C2.[XL]) [XL],
    SUM(C2.[2XL]) [2XL],
FROM CTE C1 LEFT JOIN CTE C2
ON C1.SECTIONNO>=C2.SECTIONNO
AND C1.LAYNO=C2.LAYNO

GROUP BY 
    C1.LAYNO, 
    C1.SECTIONNO

CodePudding user response：

You can use window function

SELECT SECTIONNO
  , sum([M]) over(order by SECTIONNO) [M]
  , sum([L]) over(order by SECTIONNO) [L]
  , sum([XL]) over(order by SECTIONNO) [XL]
  , sum([2XL]) over(order by SECTIONNO) [2XL]
FROM (
    SELECT SECTIONNO, SIZENAME
    FROM #TableNAME
) s 
PIVOT (
    COUNT(SIZENAME) FOR [SIZENAME] IN ([M], [L], [XL], [2XL])
) PVT 
ORDER BY PVT.SECTIONNO;