如何使用日期和Oracle中的另一列进行动态透视? [英] How to make dynamic pivot with date and another column in 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.
我需要做一个枢纽以获得每个月的QTDE或ATR的VALUE，这样结果将类似于

In IDENTIFIER, it will be only ATR or QTDE.
I need to make a pivot to get the VALUE of QTDE or ATR of every month so the result will be something like

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做，而且我也不知道如何用日期做它

I have tried to make the pivot, but I could only do with IDENTIFIER and I have no clue how to make it with the date as well

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))

我编辑表和库伦名称，这不是我表中的原始名称.

I edit the table and colunms name, it's not the original one from my table.

推荐答案

下面的示例需要在Oracle中进行重写，并且要针对您的要求.

below example needs to be rewritten in Oracle and specific to ur requirement.

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;

这篇关于如何使用日期和Oracle中的另一列进行动态透视?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！