Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Динамический pivot не доходит до PREPARE stmt FROM @sql

Тут есть
красивой работы динамического pivot для классической ситуации создания кросс-таблицы для товаров (по строкам) и свойств или показателей (по колонкам)

Но почему-то он срабатывает только в MySQL WorkBench до конца и видно в итогах эту кросс-таблицу, если сделать хранимую процедуру или выполнить его на PhpAdmin, то выполняется его первая часть.

Как и что это исправить, чтобы она возвращала финальный датасет?

К сообщению приложен файл. Размер - 46Kb
Ответ: вот тут итог в PhpAdmin

К сообщению приложен файл. Размер - 34Kb
Вопрос: PIVOT

Подскажите, как одним селектом развернуть таблицу. Мне просто нужно значения в колонки.

--работает
select * from(
select NAME  from hr_operating_units)
pivot
(max (NAME) for NAME  IN ('A', 'B'))

--нет
select * from(
select NAME  from hr_operating_units)
pivot
(max (NAME) for NAME  IN (select NAME from hr_operating_units))
Ответ:
-2-
Хардпарс все равно не видит динамических значений


Конечно, но это и не нужно. Для PIVOT FOR нам нужно знать имя поля, или, для более общего случая, SELECT получения списка значений кляузы FOR (в виде литерала естественно). SELECT мы выполним в describe и получим список значений с помощью которого сконструируем конечный SELECT, т.e. сам PIVOT. Проблема в том что мы передаем тот-же литерал-SELECT, просходит soft parse и describe уже не вызывается а значения (или их количество) возвращаемые литерал-SELECTом могли измениться.
В принципе можно тупо в SQL*Plus генерить PIVOTи тут-же выполнять сгенерированный скрипт. Или:

variable v_cur refcursor
begin
    open :v_cur for 'сгeнерированный PIVOT SQL';
end;
/
print v_cur


Но хотелось-бы чего-то систeмного, хотя, как уже не раз говорилось, все-это есть в reporting tools.

SY.
Вопрос: Заменить null в PIVOT

Хотелось бы получить PIVOT таблицу из следующего запроса

 select 
 firma, TO_CHAR(Month, 'YYYY') Year, TO_CHAR(Month, 'MM') Month, count(*) Sum
from
  result
where
  ***
group by firma, TO_CHAR(Month, 'YYYY'), TO_CHAR(Month, 'MM')
order by firma
) 
PIVOT(
sum(Sum)  
FOR Moth in ('01' as "Jan", '02' as "Feb", '03' as "March" ***)
)


Но в тех месяцах, где нет значений из основного запроса он ставит null. Попытки в разных местах использовать nvl, coalesce, case when ни к чему хорошему не привели.
Научите как справиться с ситуацией.
Спасибо заранее
Ответ:
stax..
Vint
stax..,
зачем был PIVOT когда можно было обойтись group by firma, TO_CHAR(Month, 'YYYY')+case ?

согласен на 100500 (за точность не гарантирую, учусь инетовскому сленгу)
я хотел показать
о
попробуй в самом запросе. 


я сужу по себе, иногда для меня PIVOV выдает неожиданные сообщения
и так как у меня счас общений с pivotom (да і с ораклей) мало
тупо уточніл

зы
пробую осознать mySql
по чесному не могу врубітся в фишку (кроме безплатности)
пока очень туго дается

.....
stax
В тоем запросе получилось масло масляное.
Надо либо группировку либо пивот.
select firma
      ,year
      ,nvl(jan, 0) j
      ,nvl(feb, 0) f
      ,nvl(mar, 0) m
  from (select firma, to_char(month, 'YYYY') year, to_char(month, 'MM') month, s from t)
 pivot (sum(s) for month in ('01' as jan, '02' as feb, '03' as mar))

Если не понятен смысл фичи (pivot/unpivot), то можно вспомнить как минимум
1) лучше читабельность
2) произвдительность unpivot немного лучше чем cartesian join
3) pivot for XML
4) etc
Вопрос: Pivot multiple aggregate

вопрос почему неправильно суммируются дубли в Pivot (поля DecPcs и DecAmt)? Так не получится или что-то не то с синтаксисом?

DECLARE @PivotExample AS  TABLE
(
    MONTH VARCHAR(15),
    YEAR VARCHAR(4),
    PIECES int,
    AMOUNT int
)
INSERT INTO @PivotExample VALUES
        ('Jan','2013',5,500),
        ('Jan','2014',15,2500),
        ('Feb','2013',2,300),
        ('Dec','2013',10,400),
        ('Dec','2013',10,400),
        ('Dec','2014',40,4000)


SELECT YEAR,
             MONTH+'Pcs' AS MonthPcs,
             MONTH+'Amt' AS MonthAmt,
             PIECES,
             AMOUNT
      FROM @PivotExample;

SELECT  YEAR AS Year
		,JanPcs		
		,JanAmt				
		,DecPcs
		,DecAmt
FROM (SELECT YEAR,
             MONTH+'Pcs' AS MonthPcs,
             MONTH+'Amt' AS MonthAmt,
             PIECES,
             AMOUNT
      FROM @PivotExample
	  ) AS T
      PIVOT
     (sum(PIECES) FOR MonthPcs IN
     (JanPcs,FebPcs,MarPcs,AprilPcs,MayPcs,JunePcs,JulyPcs,AugPcs,SepPcs,OctPcs,NovPcs,DecPcs)) AS P1
      PIVOT
     (sum(AMOUNT) FOR MonthAmt IN
     (JanAmt,FebAmt,MarAmt,AprilAmt,MayAmt,JuneAmt,JulyAmt,AugAmt,SepAmt,OctAmt,NovAmt,DecAmt)) AS P2;


исходные данные (добавлено поле месяца для каждого пивота)

YEARMonthPcs MonthAmt PIECES AMOUNT
2013 JanPcs JanAmt 5 500
2014 JanPcs JanAmt 15 2500
2013 FebPcs FebAmt 2 300
2013 DecPcs DecAmt 10 400
2013 DecPcs DecAmt 10 400
2014 DecPcs DecAmt 40 4000


результат запроса

Year JanPcs JanAmt DecPcs DecAmt
2013 NULL NULL 20 400
2013 NULL NULL NULL NULL
2013 5 500 NULL NULL
2014 NULL NULL 40 4000
2014 15 2500 NULL NULL


вопрос почему за 2013 год для DecPcs просуммировалось (10=10=20), а для DecAmt нет (400+400=800, а пишет 400).

если поменять значения для DecPcs таким образом

YEARMonthPcs MonthAmt PIECES AMOUNT
2013 JanPcs JanAmt 5 500
2014 JanPcs JanAmt 15 2500
2013 FebPcs FebAmt 2 300
2013 DecPcs DecAmt 10 400
2013 DecPcs DecAmt 10 500
2014 DecPcs DecAmt 40 4000


то теперь DecAmt суммируется (400+500=900), а DecPcs нет (10+10=20, а пишет 10)

Year JanPcs JanAmt DecPcs DecAmt
2013 NULL NULL 10 900
2013 NULL NULL NULL NULL
2013 5 500 NULL NULL
2014 NULL NULL 40 4000
2014 15 2500 NULL NULL


Если отдельные пивоты то все правильно суммирует.
Ответ:
Rigersong
DECLARE @PivotExample AS TABLE
(
MONTH VARCHAR(15),
YEAR VARCHAR(4),
PIECES int,
AMOUNT int
)

INSERT INTO @PivotExample
VALUES ('Jan', '2013', 5, 500),
('Jan', '2014', 15, 2500),
('Feb', '2013', 2, 300),
('Dec', '2013', 10, 400),
('Dec', '2013', 10, 400),
('Dec', '2014', 40, 4000)

SELECT YEAR
, JanPcs
, FebPcs
, DecPcs
, JanAmt
, FebAmt
, DecAmt
FROM (
SELECT YEAR
, MONTH + 'Pcs' AS MonthPcs
, MONTH + 'Amt' AS MonthAmt
, SumAmount = SUM(AMOUNT)
, SumPieces = SUM(PIECES)
FROM @PivotExample
GROUP BY PIECES, YEAR, MONTH, AMOUNT
) t
PIVOT(
SUM(SumPieces) FOR MonthPcs IN
(JanPcs, FebPcs, DecPcs)
)p1
PIVOT(
SUM(SumAmount) FOR MonthAmt IN
(JanAmt, FebAmt, DecAmt)
)p2



ага, т.е сначала просуммировать, потом перевернуть.
Одновременно не получается значит.
Вопрос: Pivot XML и group by rollup

Есть таблица вида

ORG    DE EMP     PERIOD         SAL
------ -- ------- ------- ----------
Вектор 10 Иванов  04.2015       1000
Вектор 20 Петров  04.2015       1200
Стимул 10 Сидоров 04.2015       1300
Вектор 10 Иванов  05.2015        700
Вектор 20 Петров  05.2015        900
Стимул 10 Сидоров 05.2015        400

Как в подобной выборке получить строки группировки по полям 'Org' и 'Dep' (со столбцом PIVOT XML(SUM(sal) for period IN (ANY)))?


Так не работает:
  select t.org, t.dep, t.emp, t.period, sum(t.sal), grouping(t.org) from
(
  select 'Вектор' as org,  '10' as dep ,'Иванов' as emp, '04.2015' as period, 1000 as sal from dual
   union all 
    select  'Вектор' as org,  '20' as dep , 'Петров' as emp, '04.2015' as period, 1200 as sal from dual
    union all 
     select  'Стимул' as org,  '10' as dep , 'Сидоров' as emp, '04.2015' as period, 1300 as sal from dual
    union all 
     select  'Вектор' as org,  '10' as dep , 'Иванов' as emp, '05.2015' as period, 700 as sal from dual
    union all 
     select  'Вектор' as org,  '20'  as dep ,'Петров' as emp, '05.2015' as period, 900 as sal from dual
    union all 
     select  'Стимул' as org,  '10'  as dep ,'Сидоров' as emp, '05.2015' as period, 400 as sal from dual
) t
   PIVOT XML(SUM(t.sal) for period IN (ANY))
   group by rollup(t.org)
 
Ответ:
Pivot_XML&ROLLUP
  select t.org, t.dep, t.emp, t.period, sum(t.sal), grouping(t.org) from
(
  select 'Вектор' as org,  '10' as dep ,'Иванов' as emp, '04.2015' as period, 1000 as sal from dual
   union all 
    select  'Вектор' as org,  '20' as dep , 'Петров' as emp, '04.2015' as period, 1200 as sal from dual
    union all 
     select  'Стимул' as org,  '10' as dep , 'Сидоров' as emp, '04.2015' as period, 1300 as sal from dual
    union all 
     select  'Вектор' as org,  '10' as dep , 'Иванов' as emp, '05.2015' as period, 700 as sal from dual
    union all 
     select  'Вектор' as org,  '20'  as dep ,'Петров' as emp, '05.2015' as period, 900 as sal from dual
    union all 
     select  'Стимул' as org,  '10'  as dep ,'Сидоров' as emp, '05.2015' as period, 400 as sal from dual
) t
   PIVOT XML(SUM(t.sal) for period IN (ANY))-- поворот на 90 градусов
   group by rollup(t.org)



Попробуйте написать без XML, должно проясниться.
Вопрос: pivot

Можно в pivot написать две агрегатные функции ,примерно так:

pivot(count(state),max(sum) for state in ('A','B','C'))
Ответ: puma777,

можно
pivot(count(state) as cnt, max(sum) as maxsum for state in ('A','B','C'))
Вопрос: Pivot и совместимость версий

Пытаюсь выполнить следующий код:
DECLARE @T TABLE
(
	Noms	INT,
	Name	VARCHAR(200)
)
INSERT INTO @T(Noms,Name)
VALUES (123, 'Москва')
INSERT INTO @T(Noms,Name)
VALUES (123, 'Туапсе')
INSERT INTO @T(Noms,Name)
VALUES (256, 'Москва')
INSERT INTO @T(Noms,Name)
VALUES (256, 'Львов')

SELECT Noms,	[1],	[2],	[3],	[4]
FROM 
(	SELECT 
		Noms,		Name,
		ROW_NUMBER() OVER (PARTITION BY Noms ORDER BY Noms) as Rn
	FROM @T
) as s
PIVOT
(
	MIN(Name)
	FOR Rn IN ([1],[2],[3],[4])
) as p


Мой сервер выдаёт вот такое сообщение:
Сообщение 325, уровень 15, состояние 1, строка 30
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.


по гуглил на счёт процедуры sp_dbcmptlevel, запустил её, он мне выдал вот такое:
Valid values of the database compatibility level are 60, 65, 70, 80, or 90.


Результат выполнения select @@version:
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) 


как мне запустить первый запрос, что бы он работал?
Ответ: RMagistr2015,

Мне в недавнем прошлом пришлось так делать, когда сервер 2005, а в базе выставлена совместимость 80 по требованию вендора установленной системы.

Там кроме PIVOT еще одна засада есть. OUTER APPLY в принципе выполняется в базе с уровнем 80, но значение поля в параметр табличной функции запихнуть нельзя. Тоже приходилось контекст базы подменять таким хаком.
Вопрос: PIVOT или как перевернуть таблу

Не получается перевернуть таблу. Нормально использовать PIVOT не выходит, функцию агрегирования применить не к чему.
Есть вот такие данные, немного упростил, сути это не менят
select 1 as [Код_территории]
     , N'ОТС' as [Специализация]
	 , N'535' as [Код зоны ответственности]
union all
select 1 
     , N'RX/OTX КН' 
	 , N'A245'
union all
select 1 
     , N'VC' 
	 , N'BCB9'
union all
select 2 
     , N'PH' 
	 , N'E411' as [Код_зоны_ответственности]
union all
select 2 
     , N'RX/OTX ГУД' as Spec
	 , N'81D7'
union all
select 2 
     , N'VC' 
	 , N'F679'


На выходе хочу получить вот это

select 1 as [Код_территории]
      ,N'535' as [OTC]
	  ,N'A245' as [RX/OTX КН]
	  ,N'BCB9' as [VC]
	  ,NULL AS [PH]
	  ,NULL as [RX/OTX ГУД]
union all
select 2 as [Код_территории]
      ,NULL as [OTC]
	  ,NULL as [RX/OTX КН]
	  ,N'F679' as [VC]
	  , N'E411' AS [PH]
	  , N'81D7' as [RX/OTX ГУД]


К сообщению приложен файл. Размер - 35Kb
Ответ: Рад что смог помочь... будет желание вот еще пост по поводу PIVOT -
Вопрос: Pivot c Подзапросом

Пришу вот такой вот код:
;
select * from 
(
       select version,id,comm
              from uib2.r_test
)
pivot
(
    count(id)
    for id in (select 1 as i,2 as j from dual)
)
order by version,comm
;

После апуска, выводит ошибку - Отсутствует выражение и подчеркивает select в подзапросе
почему так делает не могу понять, вроде всё по учебнику делаю (((
Помогите пожалуйста (
Ответ:
Папа, объясни мне PIVOT

Ладно, друзья )))) Пятница, видимо просто голова уже не хочет думать ))) Спасибо ))))
Вопрос: Оператор PIVOT

Приветствую вас, Друзья

Прошу вас помочь с решением данной задачи, пытался сам, но далее SELECT не зашел)

Создайте запрос, который возвращает табличное значение из таблицы Production.Product.
--Используйте оператор PIVOT.
--Результирующая таблица должна иметь поля с именами Name, RED, BLUE и BLACK и содержать значения полей
--SafetyStockLevel, Color, Name из таблицы Production.Product.
--Результирующий набор должен включать только те записи из таблицы Production.Product, для которых поле
--Name содержит строку 'Helmet'.
--Просуммируйте значения в поле SafetyStockLevel по цветам Red, Blue and Black.
--Отсортируйте результирующий набор по полю Name.
Ответ: dermama,

задача не имеет решения, т.к. в mysql оператора pivot нет.