Как сделать динамический свод с датой и другим столбцом в oracle?

ProgramBox

Как сделать динамический свод с датой и другим столбцом в oracle?

Post author:admin
Запись опубликована:19 марта, 2023
Post category:Вопросы по программированию

#sql #oracle #pivot

#sql #Oracle #сводная

Вопрос:

У меня есть такая таблица:

  NAME          VALUE       IDENTIFIER          MYDATE
NAME1           123           ATR            01/10/2018  
NAME1           333           QTDE           01/10/2018  
NAME2           212           ATR            01/08/2018  
NAME2           123           QTDE           01/08/2018 
NAME2           133           ATR            01/09/2018  
NAME2           123           QTDE           01/09/2018  
NAME3           678           ATR            01/08/2018  
NAME3           123           QTDE           01/08/2018

В IDENTIFIER , это будет только ATR или QTDE .
Мне нужно сделать сводку, чтобы получить VALUE от QTDE или ATR за каждый месяц, чтобы результат был примерно таким

 NAME        QTDE08/2018        ATR08/2018        QTDE09/2018        ATR09/2018        QTDE10/2018        ATR10/2018
NAME1           0                   0               0                   0                 333                123
NAME2          123                 212             123                 133                 0                  0
NAME3          123                 678              0                   0                  0                  0

Я пытался сделать сводку, но я мог обойтись только с IDENTIFIER , и я понятия не имею, как сделать это и с датой

 SELECT *
  FROM (SELECT ST.NAME,
               ST.VALUE,
               ST.IDENTIFIER,
               TO_CHAR(TO_DATE(ST.MYDATE), 'MM/YYYY') AS MYDATE
          FROM SOME_TABLE ST
         WHERE ST.IDENTIFIER IN ('QTDE', 'ATR')) 
PIVOT(SUM(VALUE)
      FOR IDENTIFIER IN ('QTDE' AS QTDE,
                         'ATR' AS ATR))

Я редактирую имя таблицы и colunms, оно не является оригинальным из моей таблицы.

1. Вы не можете. Кажется, вы хотите, чтобы даты (или, по крайней мере, их 'MM/YYYY' представление) исходили из данных, а не были жестко запрограммированы в запросе заранее. Это называется «динамическим поворотом», это можно сделать, но не в обычном SQL, и, как правило, это плохая практика. ЗАЧЕМ вам нужен вывод в этом формате? Если это просто для отчетности, вам может быть лучше использовать приложение для создания отчетов, которое имеет возможность выполнять такую динамическую сводку в приложении.

2. @mathguy вы сказали You can't и после This is called "dynamic pivoting", it can be done . Я не понимаю. А также, да, это для отчетности.

3. «Вы не можете», как в «вы не можете так, как вы просите», то есть в стандартном SELECT запросе. «Это можно сделать», но » вы не можете этого сделать» (в любом случае, я сомневаюсь, что вы можете.) Имеет ли это смысл?

4. Это невозможно сделать в SQL, но я могу сделать это в приложении отчетности? Кстати, я использую telerik

5. Я не знаком с telerik (и вообще с приложениями для создания отчетов в целом); Я считаю, что их код обычно является проприетарным, но нетрудно догадаться, что они, вероятно, используют динамический SQL под капотом. Дело в том, что гораздо проще научиться «делать» динамическую сводку с помощью функций приложения, чем изучать динамический SQL и писать код самостоятельно, с нуля. Узнайте, какие возможности поворота могут быть у telerik.

Ответ №1:

Вы пытаетесь преобразовать 2 столбца date и identifier, поэтому вам нужно 2 сводки. Пожалуйста, попробуйте выполнить запрос ниже, он должен работать. Не стесняйтесь возвращаться в случае каких-либо запросов. Это работает в oracle, но не уверен, пытаетесь ли вы использовать какую-либо другую СУБД.

 select * from (
select * from (
with all_data as 
(select 'NAME1' fname, 123 value, 'ATR' identif, '01-OCT-2018' mydate from dual union all
select 'NAME1' fname, 333 value, 'QTDE' identif, '01-OCT-2018'  mydate from dual union all
select 'NAME2' fname, 212 value, 'ATR' identif, '01-AUG-2018'  mydate from dual union all
select 'NAME2' fname, 123 value, 'QTDE' identif, '01-AUG-2018'  mydate from dual union all
select 'NAME2' fname, 133 value, 'ATR' identif, '01-SEP-2018'  mydate from dual union all
select 'NAME2' fname, 123 value, 'QTDE' identif, '01-SEP-2018'  mydate from dual union all
select 'NAME3' fname, 678 value, 'ATR' identif, '01-AUG-2018'  mydate from dual union all
select 'NAME3' fname, 123 value, 'QTDE' identif, '01-AUG-2018'  mydate from dual )
select identif,fname,value,mydate from  all_data)
pivot 
(
sum(value)
for mydate in ('01-AUG-2018' as "AUG-2018",'01-SEP-2018' as "SEP-2018",'01-OCT-2018' as "OCT-2018")))
pivot
(
sum("AUG-2018") "08-2018"
,sum("SEP-2018") "09-2018"
,sum("OCT-2018") "10-2018"
for identif in ('ATR' ATR,'QTDE' QTDE))
order by 1;

1. с вашим ответом мне нужно было бы перечислить все дни и все годы, чтобы заставить его работать, и каждый месяц мне нужно было бы добавлять новые строки в код, чтобы охватить все годы. Это не ответ на эту проблему, я не могу создать тысячи строк, чтобы охватить все возможные даты

2. Пожалуйста, используйте динамический sql для ввода дат и определения значений.

3. Как использовать динамический sql? У меня нет идеи, пожалуйста, объясните @sudheer

Ответ №2:

приведенный ниже пример должен быть переписан в Oracle и соответствовать требованиям ur.

 CREATE TABLE Project(PRJ_ID int, UDN_ID INT, TXT_VALUE varchar(100));

INSERT INTO Project VALUES (8344,82,'E S'),(8344,69,'A M'),(8364,82,'End'),(8364,59,'Internal');

DECLARE @columns NVARCHAR(MAX), @columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX);

--selecting distinct values and concatenating to get a result like [82],[69],[82]...


SELECT @columns1 = STUFF((
            SELECT DISTINCT ','   '['  CAST(UDN_ID AS VARCHAR)   ']' FROM Project
            FOR XML PATH('')
            ), 1, 1, '')
FROM Project;


--using that dynamic column string in the pivot query string


SET @sql = 'SELECT PRJ_ID,'   @columns1   ' FROM
(
  SELECT * FROM Project
) AS src
PIVOT
(
  MAX(TXT_VALUE) FOR src.UDN_ID IN ('  @columns1
    ')
) AS p;';


--executing the pivot query


EXEC sp_executesql @sql;