我在表中有以下資料
SECTIONNO SIZENAME
1 M
1 2XL
1 XL
1M
2M
2 XL
2 2XL
2 M
3L
3 2XL
3 XL
3L
4 M
4 2XL
4 XL
4L
5L
5 XL
5 2XL
5M
結果需要
[SECTIONNO]的智數[SIZENAME] 之前[SECTIONNO]的智數之和
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
我試過PIVOT方法，但它只給出了SECTIONNO的計數，無法讓COUNT總數結轉到下一個SECTIONNO
SELECT *
FROM (
SELECT SECTIONNO, SIZENAME
FROM TableNAME
) s
PIVOT (
COUNT(SIZENAME) FOR [SIZENAME] IN ([M], [L], [XL], [2XL])
) 獎金
ORDER BY PVT.SECTIONNO
uj5u.com熱心網友回復：
也許你可以試試這個
; with CTE AS
(
SELECT * FROM
(
SELECT
LAYNO,
SECTIONNO,
規模
FROM TableNAME
) s
PIVOT
(
COUNT(SIZENAME)
FOR [SIZENAME]
in(SIZENAME)
([M], [L], [XL], [2XL] )
) 浦東新區
)
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
GROUPBY
C1.LAYNO,
C1.SECTIONNO
uj5u.com熱心網友回復：
你可以使用視窗函式
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])
) 獎金
ORDER BY PVT.SECTIONNO;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/332777.html
標籤：