SQL Server: поворот столбцов в строки

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

SELECT TOP (1000) 
    [Машина],
    (Дата),
    [Смена],
    [ТипКоника],
    COUNT(ВесКоника) AS Машины
FROM 
    [DB_SProd].[dbo].[TableProduction]
WHERE
    ТипКоника="Высокий" OR ТипКоника="Низкий"
GROUP BY
    Машина, Дата, Смена, ТипКоника
ORDER BY
    Машина, Дата, Смена

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

Изменение колонок в строки в SQL Server с помощью PIVOT

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

Пример задачи

В вашем SQL-запросе вы хотите преобразовать информацию из таблицы TableProduction, чтобы отобразить данные по машинам, датам и сменам, а также количеству товаров по типам ("High" и "Low"). Таким образом, вы хотите, чтобы результаты были представлены в виде:

• Дата
• Смена
• Машина
• Low (количество изделий типа Low)
• High (количество изделий типа High)

Исходный SQL-запрос

Ваш исходный запрос выглядит следующим образом:

SELECT TOP (1000) 
    [Machine],
    (Date),
    [Shift],
    [ConeType],
    COUNT(ConeWeight) AS Machines
FROM 
    [DB_SProd].[dbo].[TableProduction]
WHERE
    ConeType='High' OR ConeType='Low'
GROUP BY
    Machine, Date, Shift, ConeType
ORDER BY
    Machine, Date, Shift

Этот запрос агрегации предоставляет суммарные веса по каждой комбинации Machine, Date, Shift и ConeType, но вам необходимо получить представление данных в более удобном формате.

Решение с использованием PIVOT

Для достижения нужного результата можно использовать запрос с оператором PIVOT. Этот оператор позволяет преобразовать значения из столбца ConeType в заголовки столбцов. Вот пример, как это можно сделать:

SELECT 
    [Date],
    [Shift],
    [Machine],
    ISNULL([Low], 0) AS Low,
    ISNULL([High], 0) AS High
FROM
(
    SELECT 
        [Machine],
        [Date],
        [Shift],
        [ConeType],
        COUNT(ConeWeight) AS Machines
    FROM 
        [DB_SProd].[dbo].[TableProduction]
    WHERE
        ConeType IN ('High', 'Low')
    GROUP BY
        [Machine], [Date], [Shift], [ConeType]
) AS SourceTable
PIVOT
(
    SUM(Machines)
    FOR [ConeType] IN ([Low], [High])
) AS PivotTable
ORDER BY 
    [Date], [Shift], [Machine]

Объяснение запроса

1. Вложенный SELECT: Сначала мы выбираем необходимые поля и считаем количество изделий для каждого типа, выполняя группировку по Machine, Date, Shift и ConeType.

2. Оператор PIVOT: Затем мы используем оператор PIVOT, который преобразует данные, подставляя суммы по типам ConeType (Low и High) в новые столбцы.

3. ISNULL: Мы используем функцию ISNULL, чтобы заменить NULL на 0 для тех машин, смен или дат, где не было продано изделий определенного типа. Это необходимо для создания более аккуратной отчетности.

4. ORDER BY: Наконец, мы сортируем результаты по Date, Shift и Machine для удобства восприятия.

Заключение

Использование оператора PIVOT — это мощный инструмент для преобразования данных в SQL Server. Это позволяет вам улучшить сходимость и читаемость отчета, облегчая анализ и интерпретацию данных. Не сомневайтесь в применении подобных подходов для оптимизации ваших SQL-запросов и работы с данными. Таким образом, вы сможете создавать более информативные и удобные для восприятия отчеты.