Блог

Как бы я мог создать запрос перекрестной таблицы, подобный этому?

         TopItem1 TopItem2 Category1 Ca1Item1 Ca1Item2 Category2 Ca2Sub1 Ca2Sub1It1 Ca2Sub1It2 Ca2Sub2 Ca2Sub2It1
Group1      1        3        6         2        4        4         3       1          2          1       1
Group2      3        1        0         0        0        5         4       2          2          1       1
  

т.е. запрос перекрестной таблицы с итогами по каждой категории.

Древовидная таблица с заголовками столбцов выглядит следующим образом:

TSource

 Key  Value       Parent
1    TopItem1
2    TopItem2
3    Category1
4    Category2
5    Ca1Item1    3
6    Ca1Item2    3
7    Ca2Sub1     4
8    Ca2Sub2     4
9    Ca2Sub1It1  7
10   Ca2Sub1It2  7
11   Ca2Sub2It1  8
  

таблицы с заголовками строк и значениями, подлежащими подсчету, являются такими, какими вы ожидаете их видеть.

Теперь я знаю, что совершенно невозможно создать запрос, который работал бы для любой глубины дерева, но если у меня есть только 3 уровня, как показано, как мне получить запрос перекрестной таблицы для отображения итогов по категориям?

Для относительно небольшой «глубины дерева» вы могли бы использовать ОБЪЕДИНЕНИЕ самосоединений, чтобы сопоставить суммы из дочерних записей с их родителями, бабушками и дедушками. Например, с таблицей с именем [tbl], содержащей

 Key  Value       Parent  Amount  GroupName
---  ----------  ------  ------  ---------
  1  TopItem1                 5  Group1   
  2  TopItem2                 6  Group1   
  3  Category1                7  Group1   
  4  Category2                8  Group1   
  5  Ca1Item1         3      20  Group1   
  6  Ca1Item2         3      40  Group1   
  7  Ca2Sub1          4      60  Group1   
  8  Ca2Sub2          4      80  Group1   
  9  Ca2Sub1It1       7     400  Group1   
 10  Ca2Sub1It2       7     500  Group1   
 11  Ca2Sub2It1       8     600  Group1   
  

запрос

     SELECT t.Value, t.Amount, t.GroupName FROM tbl t
UNION ALL
    SELECT t2.Value, t1.Amount, t1.GroupName
    FROM 
        tbl t1 
        INNER JOIN 
        tbl t2
            ON t1.Parent = t2.Key
UNION ALL
    SELECT t3.Value, t1.Amount, t1.GroupName
    FROM
        (
            tbl t1 
            INNER JOIN 
            tbl t2
                ON t1.Parent = t2.Key
        )
        INNER JOIN 
        tbl t3
            ON t2.Parent = t3.Key
  

вернет

• суммы для каждого «Значения» сами по себе,
• суммы для дочерних записей каждого «Значения» и
• суммы для дочерних записей каждого «Значения»

создание

 Value       Amount  GroupName
----------  ------  ---------
TopItem1         5  Group1   
TopItem2         6  Group1   
Category1        7  Group1   
Category2        8  Group1   
Ca1Item1        20  Group1   
Ca1Item2        40  Group1   
Ca2Sub1         60  Group1   
Ca2Sub2         80  Group1   
Ca2Sub1It1     400  Group1   
Ca2Sub1It2     500  Group1   
Ca2Sub2It1     600  Group1   
Category1       20  Group1   
Category1       40  Group1   
Category2       60  Group1   
Category2       80  Group1   
Ca2Sub1        400  Group1   
Ca2Sub1        500  Group1   
Ca2Sub2        600  Group1   
Category2      400  Group1   
Category2      500  Group1   
Category2      600  Group1   
  

итак, если мы обернем это в запрос перекрестной таблицы

 TRANSFORM Sum([Amount]) AS whatever
SELECT [GroupName]
FROM
    (
            SELECT t.Value, t.Amount, t.GroupName FROM tbl t
        UNION ALL
            SELECT t2.Value, t1.Amount, t1.GroupName
            FROM 
                tbl t1 
                INNER JOIN 
                tbl t2
                    ON t1.Parent = t2.Key
        UNION ALL
            SELECT t3.Value, t1.Amount, t1.GroupName
            FROM
                (
                    tbl t1 
                    INNER JOIN 
                    tbl t2
                        ON t1.Parent = t2.Key
                )
                INNER JOIN 
                tbl t3
                    ON t2.Parent = t3.Key
    )
GROUP BY [GroupName]
PIVOT [Value]
  

мы получаем

 GroupName  Ca1Item1  Ca1Item2  Ca2Sub1  Ca2Sub1It1  Ca2Sub1It2  Ca2Sub2  Ca2Sub2It1  Category1  Category2  TopItem1  TopItem2
---------  --------  --------  -------  ----------  ----------  -------  ----------  ---------  ---------  --------  --------
Group1           20        40      960         400         500      680         600         67       1648         5         6