#sql-server #dynamic-sql
Вопрос:
В настоящее время у меня есть таблица в SQL Server, которая выглядит примерно так:
| Машина | Серийный | Дата | Параметр | Ценность |
|---|---|---|---|---|
| Машина 1 | 12345 | 7/22/2021 | Парам 1 | 789 |
| Машина 1 | 12345 | 7/22/2021 | Парам 2 | 456 |
| Машина 1 | 67890 | 7/22/2021 | Парам 1 | 123 |
| Машина 1 | 67890 | 7/22/2021 | Парам 2 | 456 |
| Машина 1 | 34567 | 7/22/2021 | Парам 1 | 789 |
| Машина 1 | 34567 | 7/22/2021 | Парам 3 | 123 |
Я пытаюсь создать представление, которое преобразует эту таблицу во что-то подобное, где каждая строка определяется ее серийным номером, а заголовки представления динамически определяются параметрами в столбце «Параметр».
| Серийный | Машина | Дата | Парам 1 | Парам 2 | Парам 3 |
|---|---|---|---|---|---|
| 12345 | Машина 1 | 7/22/2021 | 789 | 456 | |
| 67890 | Машина 1 | 7/22/2021 | 123 | 456 | |
| 34567 | Машина 1 | 7/22/2021 | 789 | 123 |
Цель здесь состоит в том, чтобы исходная таблица могла содержать любое количество уникальных параметров, а результирующее представление затем группировалось по серийному номеру и заполняло каждый столбец соответствующим значением на основе присвоенного ему параметра.
До сих пор я пытался возиться с динамическим SQL, но не смог добиться никаких результатов. Я делал подобные вещи в прошлом без динамического количества параметров в столбце параметров. Возможно ли что-то подобное даже на стороне базы данных или такие манипуляции должны происходить на стороне клиента?
Комментарии:
1. Чтобы построить результирующий набор, состоящий из динамически генерируемых столбцов, да, вам понадобится динамический sql; однако вы не сможете сделать это в представлении, для этого потребуется хранимая процедура. Вы могли бы использовать свою процедуру для создания временной или постоянной таблицы для дальнейшего использования.
Ответ №1:
Если вы хотите выполнить это в SQL Server, а не на уровне представления, потребуется динамический SQL
Пример
Declare @SQL varchar(max) = '
Select *
From (
Select Serial
,Machine
,Date
,Parameter
,Value
From #YourTable
) src
Pivot (sum(Value) For [Parameter] in (' stuff((Select Distinct ',' QuoteName(Parameter) From #YourTable Order By 1 For XML Path('') ),1,1,'') ') ) pvt'
Exec(@SQL);
Результаты
Комментарии:
1. Я попробовал этот подход, но ему не нравится тот факт, что «#YourTable «в моем случае сам по себе является представлением, а не таблицей, а «Параметр» — это столбец в этом представлении. Должны ли эти объекты внутри динамической инструкции sql ссылаться на фактическую таблицу?
2. @AddisonWaege Измените #YourTable на фактическое имя ПРЕДСТАВЛЕНИЯ. Источником может быть таблица или представление, это просто не имеет значения. Просто для ясности… динамический SQL НЕ может существовать как представление или в представлении.
3. Не могли бы вы объяснить причину предложения «сумма(значение» в запросе? В моей реальной базе данных не все значения имеют один и тот же тип данных, поэтому в этой части возникает исключение.
4. @AddisonWaege PIVOT требует агрегирования. Измените значение sum() на max(). Основываясь на ваших примерных данных, я предположил, что числовые.
Ответ №2:
Одним из подходов было бы использовать операторы CASE для дополнительных столбцов в определении представления, например:
SELECT Serial,
Machine,
Date,
CASE WHEN Parameter = 'Param1' THEN Value ELSE '' END AS Param1,
CASE WHEN Parameter = 'Param2' THEN Value ELSE '' END AS Param2,
CASE WHEN Parameter = 'Param3' THEN Value ELSE '' END AS Param3
Это не будет хорошо масштабироваться до большого числа параметров, но должно хорошо работать для того, что вы описали здесь.
Если вам нужно, чтобы это было динамично или для размещения большего количества значений, вам следует рассмотреть возможность использования СВОДНОЙ
Правка: Для полноты добавим и этот подход.
DROP TABLE IF EXISTS #pivot;
CREATE TABLE #pivot
(Serial INT,
Machine VARCHAR(20),
[Date] DATETIME,
Parameter VARCHAR(6),
VALUE INT)
INSERT INTO #pivot
VALUES (12345, 'Machine 1', GETDATE(), 'Param1', 789),
(12345, 'Machine 1', GETDATE(), 'Param2', 456),
(67890, 'Machine 1', GETDATE(), 'Param1', 789),
(67890, 'Machine 1', GETDATE(), 'Param2', 456),
(34567, 'Machine 1', GETDATE(), 'Param1', 789),
(34567, 'Machine 1', GETDATE(), 'Param3', 123);
SELECT *
FROM #pivot;
SELECT Serial, Machine, Date, Param1, Param2, Param3
FROM #pivot AS p
PIVOT (
SUM(p.Value)
FOR p.Parameter
IN (Param1, Param2, Param3)
) pvt