Как развернуть таблицу на 52 недели, преодолев лимит 256 таблиц в query

Cygapb-007
Дата: 30.01.2013 17:33:28
Alexander2
к тому почему непонятно как подступиться к задаче с примемением магического PIVOT
...
не понятно как в конструкции с PIVOT в перечисление в скобках вставить динамическое условие - текущий номер недели.
Про PIVOT, или как из
OrderID  Name  Cost
-------- ----- ------
1 bar 10.00
1 foo 25.00
2 foo 15.00
3 kin 20.00
4 blat 23.00

сгенерировать (полу)автоматически
Order  bar   blat  foo   kin
------ ----- ----- ----- -----
1 10 NULL 25 NULL
2 NULL NULL 15 NULL
3 NULL NULL NULL 20
4 NULL 23 NULL NULL

По материалам Script to create dynamic PIVOT queries in SQL Server и Crosstab queries using PIVOT in SQL Server
+ применеие PIVOT для построения таблицы из итоговых данных
-- создание сводной таблицы (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;
PS раньше над PIVOT-ом не задумывался, интересно стало разобраться. Сильно не пинайте, плз))
RESEARCH
Дата: 30.01.2013 18:35:03
посмотрел приложенные атором файлы.... я бы за такое убивал ггг

слава могучему SQL что он это переваривает

case when [w52|T]   is null then 0 else [w52|T]   end

=

COALESCE (W52.T,0)
Cygapb-007
Дата: 30.01.2013 18:50:11
RESEARCH
слава могучему SQL что он это переваривает

case when [w52|T]   is null then 0 else [w52|T]   end
=
COALESCE (W52.T,0)
уже обсуждался вариант ISNULL:) 13823819
RESEARCH
Дата: 30.01.2013 19:01:37
автор
я подозреваю, должен быть лимит вложенных else, а мне надо обеспечить 52 if условия.


стиль - индийский программист, что вижу то и пишу
Alexander2
Дата: 30.01.2013 19:28:12
Cygapb-007,
Нее, пивотом пользуюсь давно, другое дело, в этой конкретной задаче, он - ни пришей, ни пристегни.
Во-первых, по сути задачи; во-вторых, уровень сервера не проглотит пивот.
Cygapb-007
Дата: 30.01.2013 21:44:37
Alexander2
Нее, пивотом пользуюсь давно, другое дело, в этой конкретной задаче, он - ни пришей, ни пристегни.
Во-первых, по сути задачи; во-вторых, уровень сервера не проглотит пивот.
То есть это не потянет?
+ сведение к PIVOT
IF OBJECT_ID(N'tempdb..#My52') IS NOT NULL
	DROP TABLE #My52;

with
--MyPERIODS(WEEK,SEQ_WEEK,PP_DAY_OF_WK,PART_ID,ORDER_IBT_ID,PLANNED,ACCT_YEAR,date)
--	as(select 0,0,0,0,0,0,0,getdate()),
--PRODUCTION_PLANNING (PART_ID,ORDER_IBT_ID,PLANNED,ACCT_YEAR,WEEK,Day_of_Week)
--	as(select 0,0,0,0,0,0),
ranges as(
	select top(1) kp.*, k52.ACCT_YEAR ACCT_YEAR_52, k52.WEEK WEEK_52
		from MyPERIODS kp
		join MyPERIODS k52 on k52.SEQ_WEEK=kp.SEQ_WEEK+52
		where GETDATE() between kp.date and kp.date+1
	),
svod as (
	SELECT 
		'w'+right(kp.SEQ_WEEK-rng.SEQ_WEEK+101,2)+'_'+kp.PP_DAY_OF_WK PIVOTER,
		kp.WEEK,
		kp.SEQ_WEEK,
		kp.PP_DAY_OF_WK as DayOfWeek,
		pp.PART_ID,
		pp.ORDER_IBT_ID,
		pp.PLANNED,
		rng.SEQ_WEEK rng_WEEK
	from MyPERIODS kp
	join ranges rng on 
		kp.ACCT_YEAR=rng.ACCT_YEAR and kp.WEEK>=rng.WEEK or kp.ACCT_YEAR=rng.ACCT_YEAR_52 and kp.WEEK<=rng.WEEK_52
	LEFT JOIN PRODUCTION_PLANNING pp on 
		kp.ACCT_YEAR = pp.ACCT_YEAR and kp.WEEK = pp.WEEK and kp.PP_DAY_OF_WK = pp.Day_of_Week
	WHERE 
		pp.PART_ID is not null and pp.ORDER_IBT_ID is not null
	)
SELECT PART_ID, ORDER_IBT_ID, PIVOTER, PLANNED 
INTO #My52 FROM svod a

-- генерация запроса на полное разворачивание колонок по всем имеющимся в PIVOTER значениям
-- (используя временную таблицу #My52 как источник данных для построения)
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- список колонок
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(x.PIVOTER)
   FROM (
      SELECT p.PIVOTER
      FROM #My52 p 
      GROUP BY p.PIVOTER
   ) AS x;
-- PIVOT-запрос
SET @sql = N'
SELECT p.PART_ID, p.ORDER_IBT_ID' + @columns + '
FROM (
   SELECT p.PART_ID, p.ORDER_IBT_ID, p.PIVOTER, p.PLANNED 
      FROM #My52 AS p
   ) AS j
   PIVOT (
      SUM(j.PLANNED)
      FOR j.PIVOTER IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')+ ')
   ) AS p;';

-- сгенерированная команда
PRINT @sql;

-- результат выполнения 
EXEC sp_executesql @sql;

DROP TABLE #My52;
Alexander2
Дата: 30.01.2013 21:58:46
Cygapb-007,
не уверен, что текущий уровень compatibility 80 проглотит "with"
я потратил целый день, вычищая фишки 2005 сервера
(пока добился такого функционала)
Alexander2
Дата: 30.01.2013 22:47:12
Cygapb-007
не, не тянет, я ж грил.
SELECT p.PART_ID, p.ORDER_IBT_ID, p.[w12_Tot]
FROM (
   SELECT p.PART_ID, p.ORDER_IBT_ID, p.PIVOTER, p.PLANNED 
      FROM #My52 AS p
   ) AS j
   PIVOT (
      SUM(j.PLANNED)
      FOR j.PIVOTER IN ([w12_Tot])
   ) AS p;

Msg 325, Level 15, State 1, Line 7
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
НЭ нада
Дата: 31.01.2013 11:37:51
Alexander2
...уровень сервера не проглотит пивот.


НЭ нада на сервер катить.
2000 под рукой нет, но думаю, он слово PIVOT вообще не знает.
а ваш в курсе дел (че, 2005, да?)
другое дело, что у кого-то база до сих пор выставлена совместимой с 2000.
и чего-бы базе не сменить compatibility level?

See help for the stored procedure sp_dbcmptlevel