Пивотирование нескольких столбцов в SQL Server

Содержание

Вопрос или проблема
Ответ или решение
Шаги для достижения желаемого результата
Пример кода SQL
Пояснения
Заключение

Вопрос или проблема

Я прочитал документацию Microsoft SQL Server о функции сводной таблицы и примерно понимаю, как она работает, но есть одна проблема, которую я не могу решить.

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

После прочтения документации по сводным таблицам SQL Server я составил этот запрос, который дает мне сводные значения колонки Tag.

Для простоты я объединил только две из 4 таблиц: Orders и Calculations и применил PIVOT().

SELECT * 
FROM
    (SELECT 
         OD.Date,
         OD.TicketNumber,
         OD.Tag,
         OD.SubTotal,
         TC.[Name] AS Calculation,
         TC.Amount
     FROM  
         @OrderData OD
     LEFT JOIN 
         @TicketCalculations TC ON TC.TicketId = OD.TicketId
     GROUP BY
         OD.Date, OD.TicketNumber,
         OD.Tag, OD.SubTotal, OD.TicketTotal,
         TC.Name, TC.Amount) AS src
PIVOT
    (SUM(src.SubTotal)
        FOR Tag IN (Food, Beer, Cocktail, Other)
    ) AS piv1

Дата	Номер билета	Расчет	Сумма	Еда	Пиво	Коктейль	Прочее
2024-09-27	5	Круг	-25.00	–	38.00	–	–
2024-09-27	6	Комиссия за доставку	-5.03	13.50	20.00	–	–
2024-09-27	6	Скидка	-2.85	13.50	20.00	–	–
2024-10-01	7	–	–	–	2.00	–	–
2024-10-01	8	–	–	–	–	4.00	–
2024-10-01	9	–	–	8.50	–	–	–

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

Это мой ожидаемый результат:

Дата	Номер билета	Еда	Газировка	Пиво	Коктейли	Прочее	Промежуточный итог	Круг	Скидка	Комиссия за доставку	Итого	Наличных	QR	Карта	Подарочный сертификат	и т.д.
27-сент	5	–	–	38	–	–	38	-25	–	–	13	13	–	–	–	–
27-сент	6	13.5	–	20	–	–	33.5	–	-2.85	-5.03	25.62	20	5.62	–	–	–
27-сент	7	–	–	2	–	–	2	–	–	–	2	–	–	–	–	–
01-окт	8	–	–	–	4	–	4	–	–	–	4	–	–	–	–	–
01-окт	9	8.5	–	–	–	–	8.5	–	–	–	8.5	–	–	–	–	–

Существует ли способ вставить сводную таблицу в другую переменную или временную таблицу? Чтобы JOIN с другой таблицей, которую я буду сводить? Или есть другой способ достичь этого?

Я не стыжусь переработать начальный запрос, если это необходимо.

Ответ или решение

Для выполнения задания по сворачиванию (pivot) нескольких столбцов в SQL Server, вы можете применить оператор PIVOT несколько раз, используя временные таблицы или переменные таблицы, чтобы последовательно агрегировать данные из нескольких источников.

Шаги для достижения желаемого результата

Создайте временную таблицу для хранения промежуточных результатов: Сначала соберите и подготовьте данные из вашей основной таблицы (например, заказов) с помощью подзапроса и сохранив его в временной таблице или переменной таблицы.
Сверните (pivot) каждые нужные поля: Используйте оператор PIVOT, чтобы сворачивать данные по каждому из полей, которые вы хотите видеть в итоговой таблице.
Объедините конечные результаты с помощью JOIN: Объедините результаты, полученные после нескольких сворачиваний, в итоговую таблицу.

Пример кода SQL

Ниже приведён пример кода, который иллюстрирует эти шаги:

-- Создаем временную таблицу
CREATE TABLE #TempData (
    Date DATE,
    TicketNumber INT,
    Tag NVARCHAR(50),
    SubTotal DECIMAL(18,2),
    Calculation NVARCHAR(50),
    Amount DECIMAL(18,2)
);

-- Заполняем временную таблицу данными
INSERT INTO #TempData
SELECT 
    OD.Date,
    OD.TicketNumber,
    OD.Tag,
    OD.SubTotal,
    TC.[Name] AS Calculation,
    TC.Amount
FROM 
    @OrderData OD
LEFT JOIN 
    @TicketCalculations TC ON TC.TicketId = OD.TicketId;

-- Сверните данные по столбцам Tag
SELECT *
INTO #PivotedTags
FROM (
    SELECT Date, TicketNumber, Tag, SubTotal
    FROM #TempData
) AS src
PIVOT (
    SUM(SubTotal) FOR Tag IN ([Food], [Beer], [Cocktail], [Other])
) AS piv1;

-- Сверните данные по столбцам Calculation
SELECT *
INTO #FinalPivot
FROM (
    SELECT 
        T.Date, 
        T.TicketNumber,
        T.Food,
        T.Beer,
        T.Cocktail,
        T.Other,
        T.SubTotal,
        DT.Calculation,
        DT.Amount
    FROM 
        #PivotedTags T
    LEFT JOIN 
        (SELECT 
            TicketNumber,
            Calculation,
            SUM(Amount) AS TotalAmount
         FROM 
            #TempData 
         GROUP BY TicketNumber, Calculation) AS DT
    ON T.TicketNumber = DT.TicketNumber
) AS src
PIVOT (
    SUM(Amount) FOR Calculation IN ([Round], [Discount], [Delivery Commission])
) AS piv2;

-- Получение итоговой таблицы с необходимыми данными
SELECT * FROM #FinalPivot;

-- Удаление временных таблиц
DROP TABLE #TempData;
DROP TABLE #PivotedTags;
DROP TABLE #FinalPivot;

Пояснения

Создание временной таблицы: Первая таблица #TempData собирает все необходимые данные, которые мы собираемся сворачивать.
Первый PIVOT: Сначала мы сворачиваем данные по Tag, создавая временную таблицу #PivotedTags.
Второй PIVOT: Затем мы выполняем сворачивание по Calculation, создавая финальную таблицу #FinalPivot.
Получение результата: В конце мы выбираем все данные из финальной таблицы.

Заключение

Таким образом, вы можете сворачивать несколько столбцов в SQL Server, используя временные таблицы. Каждый вызов для оператора PIVOT может производиться отдельно, что упрощает обработку данных и расширяет возможности для анализа. Вы также можете изменить структуру запроса в зависимости от вашей специфики данных и требований.