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 |
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 |
|
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 |
Solomon Yakobson
Messages: 3275 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 |
Solomon Yakobson
Messages: 3275 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 |
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 #666184 is a reply to message #666176] |
Wed, 18 October 2017 12:52 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
bond007 wrote on Wed, 18 October 2017 09:43Thanks 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 #666252 is a reply to message #666251] |
Mon, 23 October 2017 08:32 |
cookiemonster
Messages: 13925 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 |
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 |
cookiemonster
Messages: 13925 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 |
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 #666265 is a reply to message #666264] |
Tue, 24 October 2017 10:22 |
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 |
cookiemonster
Messages: 13925 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 |
Solomon Yakobson
Messages: 3275 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 #666320 is a reply to message #666308] |
Sat, 28 October 2017 19:19 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:20:24 CDT 2024
|