如何在oracle pivot的“in”运算符中的日期范围之间传递所有日期？

问题描述

我想要的是在PivotIN子句中传递完整的日期范围。但我正在做的是只使用我从数据库获得的值。



例如，



假设用户选择From日期为'10 / 10/2015'且To date为'10 / 15/2015'，那么我想使用所有值（10/10 / 2015,10 / 11/2015， 10/12 / 2015,10 / 13 / 2015,10 / 14 / 2015,10 / 15/2015）



但是从我的查询中发生的是（' 2015年10月10日'，'10 / 15'2015'）



现在数据是这样的：

What I want is to pass a complete range of dates in the Pivot "IN" clause. But what i am doing is that using the values only that i am getting from database.

For ex.

Suppose if user select the From date as '10/10/2015' and To date as '10/15/2015' then I want to use all the values (10/10/2015,10/11/2015,10/12/2015,10/13/2015,10/14/2015,10/15/2015)

But what is happening from my query is ('10/10/2015','10/15'2015')

Now the data is coming like this:

M_NAME PHONE JOB_ID Assigned_DATE SHIFT Assignment E_ID EMP_NAME DEPT_COLOR '10/10/2015' '10/15/2015'

但我想这样：

But I want like this:

M_NAME PHONE JOB_ID Assigned_DATE SHIFT Assignment E_ID EMP_NAME DEPT_COLOR '10/10/2015' '10/11/2015' '10/12/2015' '10/13/2015' '10/14/2015' '10/15/2015'

我的尝试：

What I have tried:

SELECT * FROM 
             (
 SELECT 
  Employee.M_NAME AS MANAGER_NAME,
  Employee.PHONE,
  Employee.JOB_ID,
  Employee.Assigned_DATE,
  Employee.SHIFT,
  Employee.Dept as Assignment, 
  Employee.E_ID, 
  Employee.NAME AS EMP_NAME,
  Employee.DEPT_COLOR
  FROM  Employee
  WHERE Assigned_Date BETWEEN
  TO_DATE('FD_Selected','MM/DD/YYYY') AND
  TO_DATE('TD_Selected','MM/DD/YYYY')
  ORDER by Employee.E_ID
             ) x
             PIVOT 
             (
                 min(Assignment)
                 FOR Assigned_Date IN (TO_DATE('FD_Selected','YYYY-MM-DD'),
                                      TO_DATE('TD_Selected','YYYY-MM-DD')
              )
          ) p 

推荐答案

SELECT * FROM (
 SELECT 
  Employee.M_NAME AS MANAGER_NAME,
  Employee.PHONE,
  Employee.JOB_ID,
  Employee.Assigned_DATE,
  Employee.SHIFT,
  Employee.Dept as Assignment, 
  Employee.E_ID, 
  Employee.NAME AS EMP_NAME,
  Employee.DEPT_COLOR
  FROM  Employee
  WHERE Assigned_Date BETWEEN TO_DATE('FD_Selected','MM/DD/YYYY') 
                      AND TO_DATE('TD_Selected','MM/DD/YYYY')
  ORDER by Employee.E_ID
  ) x
  PIVOT (
     min(Assignment)
     FOR Assigned_Date IN (TO_DATE('10/10/2015', 'YYYY-MM-DD'),
                           TO_DATE('10/11/2015', 'YYYY-MM-DD'),
                           TO_DATE('10/12/2015', 'YYYY-MM-DD'),
                           TO_DATE('10/13/2015', 'YYYY-MM-DD'),
                           TO_DATE('10/14/2015', 'YYYY-MM-DD'),
                           TO_DATE('10/15/2015', 'YYYY-MM-DD'))
  )
) p 

换句话说，在构建SQL语句本身时需要构建动态部分。

In other words you need to build the dynamic portion when building the SQL statement itself.

这篇关于如何在oracle pivot的“in”运算符中的日期范围之间传递所有日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！