Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Rows to a Single Row (oracle 10g)
Multiple Rows to a Single Row [message #666126] Mon, 16 October 2017 22:36 Go to next message
bond007
Messages: 64
Registered: March 2009
Member
Hi Experts,
Need you help to get a SQL query.
I have a table dept_inv table which contains data in the following way


DEPT Qty DATE
92 2 17-SEP-17
92 4 16-SEP-17
92 12 15-SEP-17
92 14 14-SEP-17

Lets Say Today is 17-SEP-17

I need a SQL which will give following output
DEPT Today 1DAY_AGO 2DAY_AGO 3DAY_AGO
92 2 4 12 14

Thanks in Advance
Re: Multiple Rows to a Single Row [message #666129 is a reply to message #666126] Tue, 17 October 2017 00:31 Go to previous messageGo to next message
Roots_ct5
Messages: 4
Registered: April 2011
Location: Coimbatore
Junior Member
Try this.

WITH dept_inv (DEPT, Qty ,dt) AS
  ( SELECT 92 ,2, to_date('17-SEP-17') FROM dual
  UNION ALL
  SELECT 92, 4, to_date('16-SEP-17') FROM dual
  UNION ALL
  SELECT 92 ,12, to_date('15-SEP-17') FROM dual
  UNION ALL
  SELECT 92, 14 ,to_date('14-SEP-17') FROM dual
  )
SELECT *
FROM
  ( SELECT dept,qty,dt 
    FROM dept_inv ORDER BY dt DESC
  ) PIVOT (SUM(QTY) FOR (dt) IN ('17-SEP-17' AS today,'16-SEP-17' AS "1 DAY_AGO", '15-SEP-17' AS "2 DAY_AGO",'14-SEP-17' AS "3 DAY_AGO" ) ); 


Please note: This will work only for static number of rows. If You want dynamically change the columns
You can search in google how to do that.

https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql
Re: Multiple Rows to a Single Row [message #666135 is a reply to message #666129] Tue, 17 October 2017 05:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
First of all to_date('17-SEP-17') is as bad as IN ('17-SEP-17' AS today...) since both result in implicit conversion and might produce different results for different clients. Also, imagine table has millions of rows. Your solution will fetch all of them while all we need is just four days worth of data.
Anyway, this has nothing to do with dynamic pivoting since number of columns is static. It is about picking reference date:

with dept_inv(
              dept,
              qty,
              dt
             )
  as (
      select 92,2,date '2017-09-17' from dual union all
      select 92,4,date '2017-09-16' from dual union all
      select 92,12,date '2017-09-15' from dual union all
      select 92,14,date '2017-09-14' from dual
     ),
          dt(
             ref_dt
             ) 
  as (
      select date '2017-09-17' from dual
     )
select  dept,
        sum(
            case ref_dt - dt
              when 0 then qty
            end
            ) today,
        sum(
            case ref_dt - dt
              when 1 then qty
            end
            ) "1 DAY_AGO",
        sum(
            case ref_dt - dt
              when 2 then qty
            end
            ) "2 DAY_AGO",
        sum(
            case ref_dt - dt
              when 3 then qty
            end
            ) "3 DAY_AGO"
  from  dept_inv,
        dt
  where dt between ref_dt - 3 and ref_dt
  group by dept
  order by dept
/

      DEPT      TODAY  1 DAY_AGO  2 DAY_AGO  3 DAY_AGO
---------- ---------- ---------- ---------- ----------
        92          2          4         12         14

SQL>

SY.
Re: Multiple Rows to a Single Row [message #666136 is a reply to message #666135] Tue, 17 October 2017 06:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And if you want to use pivot:

with dept_inv(
              dept,
              qty,
              dt
             )
  as (
      select 92,2,date '2017-09-17' from dual union all
      select 92,4,date '2017-09-16' from dual union all
      select 92,12,date '2017-09-15' from dual union all
      select 92,14,date '2017-09-14' from dual
     ),
          dt(
             ref_dt
             ) 
  as (
      select date '2017-09-17' from dual
     ),
            t(
              dept,
              qty,
              offset
             )
  as (
      select  dept,
              qty,
              ref_dt - dt offset
        from  dept_inv,
              dt
        where dt between ref_dt - 3 and ref_dt
     )
select  *
  from  t
  pivot(
        sum(qty)
        for offset in (0 as "TODAY",1 as "1 DAY_AGO",2 as "2 DAYS_AGO",3 as "3 DAYS_AGO")
       )
  order by dept
/

      DEPT      TODAY  1 DAY_AGO 2 DAYS_AGO 3 DAYS_AGO
---------- ---------- ---------- ---------- ----------
        92          2          4         12         14

SQL>

SY.
Re: Multiple Rows to a Single Row [message #666176 is a reply to message #666136] Wed, 18 October 2017 08:43 Go to previous messageGo to next message
bond007
Messages: 64
Registered: March 2009
Member
Thanks SY . But the above query does not work if I replace
    select 92,2,date '2017-09-17' from dual union all
      select 92,4,date '2017-09-16' from dual union all
      select 92,12,date '2017-09-15' from dual union all
      select 92,14,date '2017-09-14' from dual
with
select dept , sum(value),to_date(log_date,'YYYY-MM-DD')from my_table
group by dept,log_date
Re: Multiple Rows to a Single Row [message #666182 is a reply to message #666176] Wed, 18 October 2017 10:10 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
you could try
select dept, sum(value) as qty, to_date(log_date,'YYYY-MM-DD') as dt from my_table
group by dept,log_date
instead Cool
Re: Multiple Rows to a Single Row [message #666184 is a reply to message #666176] Wed, 18 October 2017 12:52 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
bond007 wrote on Wed, 18 October 2017 09:43
Thanks SY . But the above query does not work if I replace

[snip]

Is log_date a date? If so, then you are using dates incorrectly as you have been told.
Re: Multiple Rows to a Single Row [message #666251 is a reply to message #666184] Mon, 23 October 2017 07:47 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Also when you are putting a select in a with clause you must always use a column alias on all columns, otherwise the calling select has no way to reference the value.
Re: Multiple Rows to a Single Row [message #666252 is a reply to message #666251] Mon, 23 October 2017 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well no - you need to put an alias on anything that isn't just a plain column - if you're selecting hard-coded values from dual to mimic a table you need an alias. If you're wrapping the columns in any kind of function call you need alias.
Re: Multiple Rows to a Single Row [message #666253 is a reply to message #666252] Mon, 23 October 2017 08:58 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Of course, I though I said that.
in the statement

select col1, sum(col2) from my_table

col1 is already assigned the alias of col1, sum(col2) has no alias and needs to be assigned on or it can't be referenced.
Re: Multiple Rows to a Single Row [message #666254 is a reply to message #666253] Mon, 23 October 2017 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You just said all columns need aliasing.
And above col1 isn't assigned any alias.

There is a way of referring to things without aliases but it's a pain:
SQL> with data as (select max(dummy) from dual)
  2  select "MAX(DUMMY)" from data;
 
MAX(DUMMY)
----------
X
 
SQL> 
Re: Multiple Rows to a Single Row [message #666262 is a reply to message #666254] Tue, 24 October 2017 09:31 Go to previous messageGo to next message
bond007
Messages: 64
Registered: March 2009
Member
Table defination
desc  my_table 
Name      Null     Type      
--------- -------- --------- 
LOG_DATE  NOT NULL DATE      
DEPT      NOT NULL NUMBER(4) 
VOLUME             NUMBER  

Tried with alias but no luck
with dept_inv(dept, qty, dt  )
   as (
     select dept,volume qty,to_date(log_date,'YYYY-MM-DD') dt from  my_table 
     
     ),
   dt( ref_dt  ) 
  
  as (select date '2017-09-17' from dual  ),
   t( dept, qty,offset )   
  
  as (
      select  dept,
              qty,
              ref_dt - dt offset
        from  dept_inv,
              dt
        where dt between ref_dt - 3 and ref_dt
     )
select  * from  t
  pivot( sum(qty) for offset in (0 as "TODAY",1 as "1 DAY_AGO",2 as "2 DAYS_AGO",3 as "3 DAYS_AGO")
           )
   order by dept;
Re: Multiple Rows to a Single Row [message #666263 is a reply to message #666262] Tue, 24 October 2017 09:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what did it do - give the wrong data?
no data?
error?
Re: Multiple Rows to a Single Row [message #666264 is a reply to message #666263] Tue, 24 October 2017 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Tried with alias but no luck
totally worthless post devoid of any actionable detail.

My car won't go.
Tell me how to make my car go!
Re: Multiple Rows to a Single Row [message #666265 is a reply to message #666264] Tue, 24 October 2017 10:22 Go to previous messageGo to next message
bond007
Messages: 64
Registered: March 2009
Member
create table  my_table
( LOG_DATE  DATE 
 ,DEPT      NUMBER(4) 
 ,VOLUME     NUMBER
)


REM INSERTING into MY_TABLE
SET DEFINE OFF;
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('22-OCT-17','DD-MON-RR'),273,42509);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('22-OCT-17','DD-MON-RR'),276,-36733);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('22-OCT-17','DD-MON-RR'),400,-36843);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('23-OCT-17','DD-MON-RR'),92,997);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('23-OCT-17','DD-MON-RR'),273,76762);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('22-OCT-17','DD-MON-RR'),92,810);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('23-OCT-17','DD-MON-RR'),276,76414);
Insert into MY_TABLE (LOG_DATE,DEPT,VOLUME) values (to_date('23-OCT-17','DD-MON-RR'),400,213202);
The below SQL fetches no data
with dept_inv(dept, qty, dt  )
   as (
     select dept,volume qty,to_date(log_date,'YYYY-MM-DD') dt from  my_table 
     
     ),
   dt( ref_dt  ) 
  
  as (select date '2017-10-22' from dual  ),
   t( dept, qty,offset )   
  
  as (
      select  dept,
              qty,
              ref_dt - dt offset
        from  dept_inv,
              dt
        where dt between ref_dt - 3 and ref_dt
     )
select  * from  t
  pivot( sum(qty) for offset in (0 as "TODAY",1 as "1 DAY_AGO",2 as "2 DAYS_AGO",3 as "3 DAYS_AGO")
           )
   order by dept;
Re: Multiple Rows to a Single Row [message #666266 is a reply to message #666265] Tue, 24 October 2017 10:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'd be because you're using to_Date on a date. The results of that depend on your nls_date_format.
With mine you get:
SQL> select to_date(log_date,'YYYY-MM-DD') from my_table;
 
TO_DATE(LOG_DATE,'YYYY-MM-DD')
------------------------------
10/17/0022
10/17/0022
10/17/0022
10/17/0023
10/17/0023
10/17/0022
10/17/0023
10/17/0023
 
8 rows selected
 
SQL> 

Never to date a date. If you're worried about times not being midnight use trunc, that's what it exists for:
SQL> with dept_inv(dept, qty, dt  )
  2     as (
  3       select dept,volume qty,trunc(log_date) dt from  my_table
  4       ),
  5     dt( ref_dt  )
  6    as (select date '2017-10-22' from dual  ),
  7     t( dept, qty,offset )
  8    as (
  9        select  dept,
 10                qty,
 11                ref_dt - dt offset
 12          from  dept_inv,
 13                dt
 14          where dt between ref_dt - 3 and ref_dt
 15       )
 16  select  * from  t
 17    pivot( sum(qty) for offset in (0 as "TODAY",1 as "1 DAY_AGO",2 as "2 DAYS_AGO",3 as "3 DAYS_AGO")
 18             )
 19     order by dept;
 
 DEPT      TODAY  1 DAY_AGO 2 DAYS_AGO 3 DAYS_AGO
----- ---------- ---------- ---------- ----------
   92        810                       
  273      42509                       
  276     -36733                       
  400     -36843                       
 
SQL> 
Re: Multiple Rows to a Single Row [message #666267 is a reply to message #666265] Tue, 24 October 2017 11:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You have complete misunderstanding of DATE datatype. I suggest a lot of RTFM on DATE, DATE versus string, DATE conversion to string and string conversion to DATE, DATE formats (including NLS and defaults) before you do coding with DATEs.

SY.
Re: Multiple Rows to a Single Row [message #666304 is a reply to message #666267] Thu, 26 October 2017 07:22 Go to previous messageGo to next message
bond007
Messages: 64
Registered: March 2009
Member
Thanks a lot Solomon Yakobson and cookiemonster . You guys are the champs !!
The query works fine.
Re: Multiple Rows to a Single Row [message #666308 is a reply to message #666304] Fri, 27 October 2017 02:37 Go to previous messageGo to next message
bond007
Messages: 64
Registered: March 2009
Member
One more help needed champs Smile
Theere is a request from user to put the exact date in the column

DEPT      TODAY  1 DAY_AGO 2 DAYS_AGO 3 DAYS_AGO
----- ---------- ---------- ---------- ----------
   92        810                       
  273      42509                       
  276     -36733                       
  400     -36843   

DEPT      27-Oct-17  26-Oct-17 25-Oct-17 24-Oct-17
----- ---------- ---------- ---------- ----------
   92        810                       
  273      42509                       
  276     -36733                       
  400     -36843                       
     
Modified the code like below but getting error msg "missing comma"
 select  * from  t
    pivot( sum(qty) for offset in (0 as to_char(sysdate,'DD-MM-YY')
	                               ,1 as "1 DAY_AGO",2 as "2 DAYS_AGO",3 as "3 DAYS_AGO")
            )
     order by dept;
	 
Re: Multiple Rows to a Single Row [message #666318 is a reply to message #666308] Fri, 27 October 2017 16:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Column alias can't be expression - it a literal optionally enclosed in double quotes.

SY.
Re: Multiple Rows to a Single Row [message #666320 is a reply to message #666308] Sat, 28 October 2017 19:19 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Assuming that you always want today and the prior three days, you can loop through those using sysdate and generate and execute a select statement with pivot dynamically, as demonstrated below. I changed the dates in the sample data to correspond to today's date and the day prior for demonstration purposes.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM my_table ORDER BY log_date
  2  /

LOG_DATE              DEPT     VOLUME
--------------- ---------- ----------
Fri 27-Oct-2017        400     -36843
Fri 27-Oct-2017         92        810
Fri 27-Oct-2017        276     -36733
Fri 27-Oct-2017        273      42509
Sat 28-Oct-2017        276      76414
Sat 28-Oct-2017        273      76762
Sat 28-Oct-2017        400     213202
Sat 28-Oct-2017         92        997

8 rows selected.

SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    v_sql  VARCHAR2(32767);
  3  BEGIN
  4    v_sql :=
  5  	 'SELECT *
  6  	  FROM	 (SELECT dept, volume, TO_CHAR(log_date,''DD-MON-YYYY'') dt
  7  		  FROM	 my_table
  8  		  WHERE  TRUNC(log_date) >= TRUNC(SYSDATE-3))
  9  	  PIVOT  (SUM(volume) FOR (dt) IN (';
 10    FOR i IN
 11  	 (SELECT  TO_CHAR(SYSDATE-(ROWNUM-1),'DD-MON-YYYY') dateval
 12  	  FROM	  DUAL
 13  	  CONNECT BY LEVEL <= 4)
 14    LOOP
 15  	 v_sql := v_sql || '''' || i.dateval || ''',';
 16    END LOOP;
 17    v_sql := RTRIM(v_sql,',') || ')) ORDER BY dept';
 18    -- DBMS_OUTPUT.PUT_LINE(v_sql);
 19    OPEN :g_ref FOR v_sql;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> PRINT g_ref

      DEPT '28-OCT-2017' '27-OCT-2017' '26-OCT-2017' '25-OCT-2017'
---------- ------------- ------------- ------------- -------------
        92           997           810
       273         76762         42509
       276         76414        -36733
       400        213202        -36843

4 rows selected.

Previous Topic: Error 500--Internal Server Error while running oracle Reports to print the pdf in 11g R2[11.1.2.2.0]
Next Topic: NO_CPU_COSTING hint
Goto Forum:
  


Current Time: Thu Mar 28 06:40:23 CDT 2024