у меня в базе данных есть таблицы [code] SELECT TOP (1000) [WorkId] ,[StatusId]…
- User
у меня в базе данных есть таблицы
SELECT TOP (1000) [MaterialId] ,[ProjectId] ,[BOCount] ,[ConsumptionCoefficient] ,[ContractorComment] ,[CostName] ,[ISRComment] ,[IsMigrated] ,[MatEquipmCost] ,[MatEquipmPrice] ,[NodeCode] ,[NumberItem] ,[ParentWorkId] ,[SmpPnpCost] ,[SmpPnpPrice] ,[TotalCost] ,[TotalCount] ,[TotalPrice] ,[TypeCost] ,[UnitId] ,[UnitOfMeasure] FROM [BuildingFlow].[dbo].[Material]SELECT TOP (1000) [WorkId] ,[StatusId] ,[BOCount] ,[Comment] ,[ConsumptionCoefficient] ,[ContractorComment] ,[CostName] ,[EndDate] ,[HierarchyLevel] ,[ISRComment] ,[IsMigrated] ,[NodeCode] ,[NumberItem] ,[ParentWorkId] ,[ProjectId] ,[StartDate] ,[TotalCount] ,[UnitId] ,[UnitOfMeasure] ,[MatEquipmCost] ,[MatEquipmPrice] ,[SmpPnpCost] ,[SmpPnpPrice] ,[TotalCost] ,[TotalPrice] FROM [BuildingFlow].[dbo].[Work]
SELECT TOP (1000) [WorkMaterialId] ,[WorkId] ,[MaterialId] ,[Quantity] FROM [BuildingFlow].[dbo].[WorkMaterial]напиши запрос который выберет поля [NumberItem], [NodeCode], [HierarchyLevel], [CostName] и создаст сводную таблицу
- Assistant
Для создания сводной таблицы с полями
[NumberItem],[NodeCode],[HierarchyLevel],[CostName], можно использовать операторPIVOTв SQL Server. Предположим, что вы хотите агрегировать суммуTotalCostпо каждомуCostNameдля каждой комбинацииNumberItem,NodeCodeиHierarchyLevel.Вот пример запроса:
SELECT NumberItem, NodeCode, HierarchyLevel, [CostName1], [CostName2], [CostName3] -- Добавьте дополнительные CostName по необходимости FROM ( SELECT W.NumberItem, W.NodeCode, W.HierarchyLevel, W.CostName, W.TotalCost FROM [BuildingFlow].[dbo].[Work] W ) AS SourceTable PIVOT ( SUM(TotalCost) FOR CostName IN ([CostName1], [CostName2], [CostName3]) -- Укажите здесь все необходимые CostName ) AS PivotTable ORDER BY NumberItem, NodeCode, HierarchyLevel;Пояснения:
-
Внутренний запрос (
SourceTable):- Извлекает необходимые поля из таблицы
[Work]. - Если требуется включить данные из других таблиц (например,
[Material]или[WorkMaterial]), добавьте соответствующиеJOIN.
- Извлекает необходимые поля из таблицы
-
Оператор
PIVOT:- Агрегирует
TotalCostпо каждомуCostName. - В разделе
INперечислите все значенияCostName, которые хотите видеть как столбцы в сводной таблице. Если списокCostNameдинамический или слишком большой, рассмотрите использование динамического SQL.
- Агрегирует
-
SELECT вне
PIVOT:- Выбирает итоговые столбцы, включая агрегированные значения по каждому
CostName. - Не забудьте заменить
[CostName1], [CostName2], [CostName3]на реальные значения из ваших данных.
- Выбирает итоговые столбцы, включая агрегированные значения по каждому
Динамическое Pivot (если список
CostNameнеизвестен заранее):Если список
CostNameможет меняться или слишком велик, можно использовать динамический SQL для построения запроса. Вот пример:DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX); -- Получение списка уникальных CostName SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CostName) FROM [BuildingFlow].[dbo].[Work] FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); -- Формирование динамического запроса SET @query = ' SELECT NumberItem, NodeCode, HierarchyLevel, ' + @cols + ' FROM ( SELECT W.NumberItem, W.NodeCode, W.HierarchyLevel, W.CostName, W.TotalCost FROM [BuildingFlow].[dbo].[Work] W ) AS SourceTable PIVOT ( SUM(TotalCost) FOR CostName IN (' + @cols + ') ) AS PivotTable ORDER BY NumberItem, NodeCode, HierarchyLevel; '; -- Выполнение динамического запроса EXEC sp_executesql @query;Объяснение динамического Pivot:
-
Сбор уникальных
CostName:- Используется
STUFFиFOR XML PATHдля создания списка столбцов в формате[CostName1],[CostName2],....
- Используется
-
Построение строки запроса:
- Включает полученные столбцы в оператор
PIVOT.
- Включает полученные столбцы в оператор
-
Исполнение запроса:
- С помощью
sp_executesqlвыполняется сформированный SQL-запрос.
- С помощью
Этот подход позволяет автоматически учитывать все существующие значения
CostNameбез необходимости вручную обновлять список столбцов.Дополнительные рекомендации:
- Проверка данных: Убедитесь, что данные в
CostNameкорректны и не содержат неожиданных или нежелательных значений. - Оптимизация производительности: При работе с большими таблицами убедитесь, что соответствующие поля индексированы для повышения скорости выполнения запроса.
- Форматирование результатов: При необходимости вы можете дополнительно форматировать числовые значения, например, использовать
FORMATдля отображения с определённым количеством знаков после запятой.
Если у вас есть дополнительные требования или уточнения по сводной таблице, пожалуйста, сообщите, и я помогу адаптировать запрос под ваши нужды.
-