限制列數的交叉表

限制列數的交叉資料報表

--樣本資料:
CREATE TABLE test(
factoryid varchar(20),
bagid int,
roll int,
number numeric(9,1),
UNIQUE(bagid,roll))
INSERT test SELECT 'M-CS-11#6/GREEN',1,1, 86
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,2, 59.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,3, 31.2
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,4, 42
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,5, 31
UNION ALL   SELECT 'M-CS-11#6/GREEN',1,6, 114.3

UNION ALL   SELECT 'M-CS-11#6/GREEN',2,7, 101
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,8, 83.9
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,9, 97.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',2,10,105.4

UNION ALL   SELECT 'M-CS-11#6/GREEN',3,11,103
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,12,128.5
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,13,74.7
UNION ALL   SELECT 'M-CS-11#6/GREEN',3,14,107

UNION ALL   SELECT 'M-CS-11#6/GREEN',4,15,73.4
UNION ALL   SELECT 'M-CS-11#6/GREEN',4,16,100
UNION ALL   SELECT 'M-CS-11#6/GREEN',4,17,141.5
GO

問題描述:
    bagid,roll值唯一，需要將列roll水平顯示，並且每條記錄只顯示4列，多餘的自動換行。對於樣本資料，要求結果如下（rolls是記錄數）：
factoryid                        bagid   rolls    n1      n2        n3        n4          Total  
---------------------------- --------- -------- -------- --------- --------- ---------- --------------
M-CS-11#6/GREEN   1         6          86.0    59.5     31.2     42.0    
M-CS-11#6/GREEN   1                     31.0    114.3                               364.0
M-CS-11#6/GREEN   2         4         101.0   83.9     97.5     105.4    387.8
M-CS-11#6/GREEN   3         4         103.0   128.5   74.7     107.0    413.2
M-CS-11#6/GREEN   4         3         73.4     100.0   141.5                  314.9
Total                                                                                                           1479.9

（所影響的行數為 6 行）

--查詢處理代碼
SELECT a.factoryid,a.bagid,
    rolls=CASE 
        WHEN a.roll=0 THEN CAST(b.rolls as varchar)
        ELSE '' END,
    a.n1,a.n2,a.n3,a.n4,
    Total=CASE
        WHEN a.roll IS NULL THEN CAST(a.Total as varchar)
        WHEN a.roll=(b.rolls-1)/4 THEN CAST(b.Total as varchar)
        ELSE '' END
FROM(
        SELECT factoryid=CASE 
                WHEN GROUPING(factoryid)=1 THEN 'Total'
                ELSE factoryid END,
            bagid=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE CAST(bagid AS VARCHAR) END,
            n1=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE CAST(SUM(CASE roll%4 WHEN 0 THEN number END) AS VARCHAR) END,
            n2=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 1 THEN number END) AS VARCHAR),'') END,
            n3=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 2 THEN number END) AS VARCHAR),'') END,
            n4=CASE
                WHEN GROUPING(factoryid)=1 THEN ''
                ELSE ISNULL(CAST(SUM(CASE roll%4 WHEN 3 THEN number END) AS VARCHAR),'') END,
            Total=SUM(number),
            roll=roll/4
        FROM(
            SELECT factoryid,bagid,number,
                roll=(SELECT COUNT(DISTINCT roll) 
                    FROM test 
                    WHERE factoryid=a.factoryid
                        AND bagid=a.bagid
                        AND roll<a.roll)
            FROM test a
        )a GROUP BY factoryid,bagid,roll/4 WITH ROLLUP
        HAVING GROUPING(factoryid)=1 OR GROUPING(roll/4)=0
    )a
    LEFT JOIN(
        SELECT factoryid,bagid,
            rolls=COUNT(*),
            Total=SUM(number)
        FROM test
        GROUP BY factoryid,bagid
    )b ON a.factoryid=b.factoryid
        AND a.bagid=b.bagid
GO

原帖地址