Sample table:

output should be:

here, I should display, add 1 for the date upto 7days and sal should be divided by 7

Each row from the sample input should result as 7 rows in the sample output

I need a query in Oracle SQL

CodePudding user response：

You can CROSS JOIN with a row generator to create the 7 days:

SELECT t.emp,
       t."DATE"   d.days AS "DATE",
       TRUNC(t.sal / 7) AS sal
FROM   table_name t
       CROSS JOIN (
         SELECT LEVEL - 1 AS days
         FROM   DUAL
         CONNECT BY LEVEL <= 7
       ) d
ORDER BY emp, "DATE"

Which, for the sample data:

CREATE TABLE table_name (emp, "DATE", sal) AS
SELECT 698, DATE '2021-11-28', 9200 FROM DUAL UNION ALL
SELECT 724, DATE '2021-01-02', 8700 FROM DUAL;

Outputs:

EMP	DATE	SAL
698	2021-11-28 00:00:00	1314
698	2021-11-29 00:00:00	1314
698	2021-11-30 00:00:00	1314
698	2021-12-01 00:00:00	1314
698	2021-12-02 00:00:00	1314
698	2021-12-03 00:00:00	1314
698	2021-12-04 00:00:00	1314
724	2021-01-02 00:00:00	1242
724	2021-01-03 00:00:00	1242
724	2021-01-04 00:00:00	1242
724	2021-01-05 00:00:00	1242
724	2021-01-06 00:00:00	1242
724	2021-01-07 00:00:00	1242
724	2021-01-08 00:00:00	1242

db<>fiddle here

CodePudding user response：

Here's a compact way to do this - using a simple XQuery expression to generate numbers between 0 and 6:

select emp,
       date_   xmlcast(column_value as number) as date_,
       round(sal/7, 2) as sal
from   table_name cross join xmltable('0 to 6')
;

Note - I changed the date column name to date_ (with an underscore). date is a reserved keyword, it can't be a column name. Also, obviously, you will need to use your actual table name.