-- создание сводной таблицы (PIVOT) по итоговым данным
-- компиляция двух тем из www.mssqltips.com
-- 1) http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
-- 2) http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/
IF OBJECT_ID('TempDB..#Products','U') IS NOT NULL DROP TABLE #Products
IF OBJECT_ID('TempDB..#OrderDetails','U') IS NOT NULL DROP TABLE #OrderDetails
GO
CREATE TABLE #Products (ProductID INT PRIMARY KEY, Name NVARCHAR(255) NOT NULL UNIQUE /* other columns */);
INSERT #Products VALUES (1, N'foo'),(2, N'bar'),(3, N'kin');
CREATE TABLE #OrderDetails(OrderID INT, ProductID INT NOT NULL /*FOREIGN KEY REFERENCES #Products(ProductID)*/, Quantity INT, Cost money /* other columns */);
INSERT #OrderDetails VALUES (1, 1, 1, 25.00),(1, 2, 2, 10.00),(2, 1, 1, 15.00),(3, 3, 1, 20.00);
/*
-- итоговая информация "плоско"
SELECT o.OrderID, p.Name, Cost = SUM(o.Cost)
FROM #Products AS p
INNER JOIN #OrderDetails AS o ON p.ProductID = o.ProductID
GROUP BY o.OrderID, p.Name;
-- сводная таблица по первой и второй колонкам итоговой информации
-- (с явным указанием всех значений второй колонки)
SELECT pvt.*
FROM (
SELECT d.OrderID, p.Name, d.Cost
FROM #Products p
JOIN #OrderDetails d on d.ProductID=p.ProductID
) data
PIVOT (
sum(data.Cost)
for data.Name in ([foo], [bar], [kin])
) pvt
-- после добавления во вторую колонку новых значений
INSERT #Products SELECT 4, N'blat';
INSERT #OrderDetails SELECT 4,4,5, 23.00;
-- эти новые значения необходимо указать в списке разворачиваемых горизонтально колонок
-- иначе они там не появятся
SELECT pvt.*
FROM (
SELECT d.OrderID, p.Name, d.Cost
FROM #Products p
JOIN #OrderDetails d on d.ProductID=p.ProductID
) data
PIVOT (
sum(data.Cost)
for data.Name in ([foo], [bar], [kin])
-- for data.Name in ([foo], [bar], [kin], [blat])
) pvt
*/
-- Фиксация данных, необходимых для построения PIVOT, во временной таблице с 3 колонками
-- (значения для идентификации строк, названия колонок и данные для аггрегирования)
IF OBJECT_ID('TempDB..#PivotData','U') IS NOT NULL DROP TABLE #PivotData
CREATE TABLE #PivotData (RowId int, ColName nvarchar(100), Value float)
INSERT #PivotData
SELECT o.OrderID, p.Name, Cost = SUM(o.Cost)
FROM #Products AS p
INNER JOIN #OrderDetails AS o ON p.ProductID = o.ProductID
GROUP BY o.OrderID, p.Name;
-- SELECT * FROM #PivotData
-- генерация запроса на полное разворачивание колонок по всем имеющимся значениям
-- (используя временную таблицу #PivotData как источник данных для построения)
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- список колонок
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(x.ColName)
FROM (
SELECT p.ColName
FROM #PivotData p
GROUP BY p.ColName
) AS x;
-- PIVOT-запрос
SET @sql = N'
SELECT p.RowId [Order]' + @columns + '
FROM (
SELECT p.RowId, p.ColName, p.Value
FROM #PivotData AS p
) AS j
PIVOT (
SUM(j.Value)
FOR j.ColName IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')+ ')
) AS p;';
-- сгенерированная команда
PRINT @sql;
-- результат выполнения
EXEC sp_executesql @sql;
DROP TABLE #PivotData;
|