Home » SQL & PL/SQL » SQL & PL/SQL » Group pivot column (11.2.0.1.0)
Group pivot column [message #682071] |
Sun, 04 October 2020 05:00 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have the following table and pivot query:
CREATE TABLE TEST_PIV (
YEAR NUMBER,
PROJECT_SECTOR_ID NUMBER(2, 0) NOT NULL,
NETLOAN NUMBER);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 7, 8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 5, 29.76);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1984, 5, 4.8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1984, 3, 3.79);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1991, 6, 9.4);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1991, 4, 10.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1992, 5, 8.85);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 4, 7.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 5, 2.3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 2, 2.38);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 2, 2);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1990, 3, 10.99);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 1, 6);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 5, 2);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1981, 2, 1);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1988, 1, 5.58);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1980, 2, 3.93);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1980, 5, 3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1987, 1, 7);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 1, 17.24);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1976, 5, 20.96);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 1, 2.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 7, 29.42);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 1, 3.9);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1986, 3, 11.64);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 3, 41.63);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 6, 17.39);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 4, 11.26);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 3, 2.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1985, 8, 0.53);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 1, 9.83);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 1, 67.68);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 7, 3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 1, 48.12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2002, 6, 26.15);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1985, 8, 1.11);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1999, 3, 25.03);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 1, 5.54);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 6, 9.91);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 4, 7);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 2, 0.55);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 6, 9);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1976, 6, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2017, 1, 15);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1996, 3, 3.21);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2014, 4, 3.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1999, 3, 3.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2000, 5, 8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 7, 23);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2000, 3, 12);
select * FROM
( select Year, PROJECT_SECTOR_ID, netloan from test_piv)
pivot
(
sum(netloan)
for PROJECT_SECTOR_ID in (1,2,3,4,5,6,7,8)
)
order by year;
However instead of displaying each year (1975, 1976, 1977... etc.) I need to group the year column so it shows every 5 years together except for the last five years (they should appear separately). so I need the year column to show:
1975-1979
1980-1984
1985-1989
...
2010-2014
2015-2015 (or just 2015)
2016
2017
2018
2019
2020
Thanks,
Ferro
|
|
|
Re: Group pivot column [message #682072 is a reply to message #682071] |
Sun, 04 October 2020 05:54 |
|
Michel Cadot
Messages: 68669 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col year format a10
SQL> select * FROM
2 ( select case
3 when year < 2015
4 then to_char(trunc(year/5)*5)||'-'||to_char(trunc(year/5)*5+4)
5 else to_char(year)
6 end Year,
7 PROJECT_SECTOR_ID, sum(netloan) netloan
8 from test_piv
9 group by PROJECT_SECTOR_ID,
10 case
11 when year < 2015
12 then to_char(trunc(year/5)*5)||'-'||to_char(trunc(year/5)*5+4)
13 else to_char(year)
14 end
15 )
16 pivot
17 (
18 sum(netloan)
19 for PROJECT_SECTOR_ID in (1,2,3,4,5,6,7,8)
20 )
21 order by year
22 /
YEAR 1 2 3 4 5 6 7 8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975-1979 23.64 .55 7 20.96 30.91
1980-1984 6 9.31 3.79 7.5 12.1
1985-1989 12.58 11.64 1.64
1990-1994 55.12 21.76 38.61 26.79 8
1995-1999 15.37 31.74 52.42
2000-2004 115.8 12 8 26.15 3
2010-2014 3.5
2017 15
8 rows selected.
[Updated on: Sun, 04 October 2020 05:56] Report message to a moderator
|
|
|
Re: Group pivot column [message #682073 is a reply to message #682071] |
Sun, 04 October 2020 06:25 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Years in your test case start with 1975 so it isn't clear you you want to group years into group ending 0 to 4 and 5 to 9 regardless of oldest year in the table or group years in groups of five years starting with the oldest year in the table. For former use:
with t as (
select (year - mod(year,5)) || ' - ' || (year - mod(year,5) + 4) years,
project_sector_id,
netloan
from test_piv
)
select *
from t
pivot(
sum(netloan)
for project_sector_id in (1,2,3,4,5,6,7,8)
)
order by years
/
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2015 - 2019 15
8 rows selected.
SQL>
For latter:
with t as (
select (year - mod(year - mod(min(year) over(),5),5)) || ' - ' || (year - mod(year - mod(min(year) over(),5),5) + 4) years,
project_sector_id,
netloan
from test_piv
)
select *
from t
pivot(
sum(netloan)
for project_sector_id in (1,2,3,4,5,6,7,8)
)
order by years
/
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2015 - 2019 15
8 rows selected.
SQL>
SY.
|
|
|
Re: Group pivot column [message #682074 is a reply to message #682073] |
Sun, 04 October 2020 06:37 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ah, I missed last 5 years part. Again, not clear if last five years based on today or on table data. I'll assume based on today:
with t as (
select case
when year >= to_number(to_char(sysdate,'yyyy')) - 4 then to_char(year)
else (year - mod(year,5)) || ' - ' || (year - mod(year,5) + 4)
end years,
project_sector_id,
netloan
from test_piv
)
select *
from t
pivot(
sum(netloan)
for project_sector_id in (1,2,3,4,5,6,7,8)
)
order by years
/
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2017 15
8 rows selected.
SQL>
SY.
|
|
|
Re: Group pivot column [message #682075 is a reply to message #682074] |
Sun, 04 October 2020 07:02 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ah, ending year should be adjusted, otherwise:
SQL> insert into test_piv
2 values(2015,3,1000)
3 /
1 row created.
SQL> with t as (
2 select case
3 when year >= to_number(to_char(sysdate,'yyyy')) - 4 then to_char(year)
4 else (year - mod(year,5)) || ' - ' || (year - mod(year,5) + 4)
5 end years,
6 project_sector_id,
7 netloan
8 from test_piv
9 )
10 select *
11 from t
12 pivot(
13 sum(netloan)
14 for project_sector_id in (1,2,3,4,5,6,7,8)
15 )
16 order by years
17 /
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2015 - 2019 1000
2017 15
9 rows selected.
SQL>
As you can see, 2015 - 2019 is wrong. Adjusted query:
with t as (
select case
when year > to_number(to_char(sysdate,'yyyy')) - 5 then to_char(year)
else (year - mod(year,5)) || ' - ' || least(to_number(to_char(sysdate,'yyyy')) - 5,year - mod(year,5) + 4)
end years,
project_sector_id,
netloan
from test_piv
)
select *
from t
pivot(
sum(netloan)
for project_sector_id in (1,2,3,4,5,6,7,8)
)
order by years
/
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2015 - 2015 1000
2017 15
9 rows selected.
SQL>
SY.
|
|
|
|
Re: Group pivot column [message #682077 is a reply to message #682076] |
Sun, 04 October 2020 07:36 |
|
Michel Cadot
Messages: 68669 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Post INSERT statement with data in these years we could search at our side.
And clarify your specification as Solomon pointed.
Are the 5 years interval always 0-4 and 5-9, or 5 years interval before the current year (that is for 2021, 1-5 and 6-0, for 2022 2-6, 7-1)? Or 5 years starting from the first year in the data (that is if first year is 1974 then 4-8, 9-3, if it is 1973 3-7 and 8-2)?
[Updated on: Sun, 04 October 2020 07:43] Report message to a moderator
|
|
|
Re: Group pivot column [message #682078 is a reply to message #682077] |
Sun, 04 October 2020 08:08 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear Michel,
Thanks for your follow up.
The data can start at any year (in fact the actual data starts in 1974 which is handled perfectly by last example which adds 1970-1974 interval. So it is safe to keep it this way for older years. The issue is in handling the last years as the repetition leads to confusing results (having an internal for 2015-2019, then having the years 2016,...,2019 separately).
The table with the new data:
drop table TEST_PIV;
CREATE TABLE TEST_PIV (
YEAR NUMBER,
PROJECT_SECTOR_ID NUMBER(2, 0) NOT NULL,
NETLOAN NUMBER);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 7, 8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 5, 29.76);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1984, 5, 4.8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1984, 3, 3.79);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1991, 6, 9.4);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1991, 4, 10.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1992, 5, 8.85);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 4, 7.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 5, 2.3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1983, 2, 2.38);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 2, 2);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1990, 3, 10.99);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 1, 6);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1982, 5, 2);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1981, 2, 1);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1988, 1, 5.58);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1980, 2, 3.93);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1980, 5, 3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1987, 1, 7);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 1, 17.24);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1976, 5, 20.96);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 1, 2.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 7, 29.42);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 1, 3.9);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1986, 3, 11.64);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 3, 41.63);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 6, 17.39);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1993, 4, 11.26);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1994, 3, 2.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1985, 8, 0.53);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 1, 9.83);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 1, 67.68);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 7, 3);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2001, 1, 48.12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2002, 6, 26.15);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1985, 8, 1.11);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1999, 3, 25.03);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 1, 5.54);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 6, 9.91);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1977, 4, 7);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 2, 0.55);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1975, 6, 9);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1976, 6, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2017, 1, 15);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1996, 3, 3.21);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2014, 4, 3.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1999, 3, 3.5);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2000, 5, 8);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1997, 7, 23);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2000, 3, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1974, 3, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2015, 8, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2016, 1, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2017, 3, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2018, 1, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2019, 6, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2020, 3, 12);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(1974, 3, 70);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2015, 8, 16);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2016, 1, 23);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2017, 3, 15);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2018, 1, 22);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2019, 6, 17);
INSERT INTO TEST_PIV(YEAR, PROJECT_SECTOR_ID, NETLOAN) VALUES
(2020, 3, 6);
Thanks,
|
|
|
Re: Group pivot column [message #682079 is a reply to message #682076] |
Sun, 04 October 2020 08:13 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
OraFerro wrote on Sun, 04 October 2020 08:08
@SY, your last example runs very fast in large data and is completely dynamic with future years, however if the output data contain years 2015, 2016, 2017, 2018, 2019, 2020; the result will include:
2015-2019 (holding only the data for 2015 followed by each year separately)
2016
2017
2018
2019
2020
I am trying to figure out how to add an extra condition that doesnt have hard wiring the year 2015 and could get me (2015-2015) in case the last group has only one year or less than 5 years.
Thanks,
Ferro
Not sure I understand why would you want to show 2015-2019 if it is holding only the data for 2015? My one before last reply does this.
So you want to list 2016, 2017, 2018, 2019 and 2020 even though they have no data? If so:
with t1 as (
select *
from test_piv
union all
select to_number(to_char(sysdate,'yyyy')) + 1 - level,
1,
null
from dual
connect by level <= 6
),
t2 as (
select case
when year > to_number(to_char(sysdate,'yyyy')) - 5 then to_char(year)
else (year - mod(year,5)) || ' - ' || least(to_number(to_char(sysdate,'yyyy')) - 5,year - mod(year,5) + 4)
end years,
project_sector_id,
netloan
from t1
)
select *
from t2
pivot(
sum(netloan)
for project_sector_id in (1,2,3,4,5,6,7,8)
)
order by years
/
YEARS 1 2 3 4 5 6 7 8
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975 - 1979 23.64 .55 7 20.96 30.91
1980 - 1984 6 9.31 3.79 7.5 12.1
1985 - 1989 12.58 11.64 1.64
1990 - 1994 55.12 21.76 38.61 26.79 8
1995 - 1999 15.37 31.74 52.42
2000 - 2004 115.8 12 8 26.15 3
2010 - 2014 3.5
2015 - 2015
2016
2017 15
2018
2019
2020
13 rows selected.
SQL>
SY.
[Updated on: Sun, 04 October 2020 08:19] Report message to a moderator
|
|
|
Re: Group pivot column [message #682081 is a reply to message #682078] |
Sun, 04 October 2020 11:10 |
|
Michel Cadot
Messages: 68669 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col years format a10
SQL> with
2 refer as (
3 select min(trunc(year/5)*5) min_year,
4 extract(year from sysdate)-5 max_year
5 from test_piv
6 ),
7 cal as (
8 select min_year+5*(level-1) start_year,
9 least(min_year+5*(level-1)+4,max_year) end_year
10 from refer
11 connect by level <= trunc((max_year-min_year)/5)+1
12 union all
13 select max_year+level, max_year+level
14 from refer
15 connect by level <= 5
16 )
17 select *
18 from ( select to_char(c.start_year)||
19 decode(c.end_year,c.start_year,'','-'||to_char(c.end_year)) years,
20 t.project_sector_id, sum(t.netloan) netloan
21 from cal c left outer join test_piv t
22 on t.year between c.start_year and c.end_year
23 group by project_sector_id,
24 to_char(c.start_year)||
25 decode(c.end_year,c.start_year,'','-'||to_char(c.end_year))
26 )
27 pivot (sum(netloan) for project_sector_id in (1,2,3,4,5,6,7,8))
28 order by years
29 /
YEARS 1 2 3 4 5 6 7 8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1970-1974 82
1975-1979 23.64 .55 7 20.96 30.91
1980-1984 6 9.31 3.79 7.5 12.1
1985-1989 12.58 11.64 1.64
1990-1994 55.12 21.76 38.61 26.79 8
1995-1999 15.37 31.74 52.42
2000-2004 115.8 12 8 26.15 3
2005-2009
2010-2014 3.5
2015 28
2016 35
2017 15 27
2018 34
2019 29
2020 18
|
|
|
Re: Group pivot column [message #682082 is a reply to message #682081] |
Sun, 04 October 2020 11:13 |
|
Michel Cadot
Messages: 68669 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or with previous set of data like Solomon:
SQL> col years format a10
SQL> with
2 refer as (
3 select min(trunc(year/5)*5) min_year,
4 extract(year from sysdate)-5 max_year
5 from test_piv
6 ),
7 cal as (
8 select min_year+5*(level-1) start_year,
9 least(min_year+5*(level-1)+4,max_year) end_year
10 from refer
11 connect by level <= trunc((max_year-min_year)/5)+1
12 union all
13 select max_year+level, max_year+level
14 from refer
15 connect by level <= 5
16 )
17 select *
18 from ( select to_char(c.start_year)||
19 decode(c.end_year,c.start_year,'','-'||to_char(c.end_year)) years,
20 t.project_sector_id, sum(t.netloan) netloan
21 from cal c left outer join test_piv t
22 on t.year between c.start_year and c.end_year
23 group by project_sector_id,
24 to_char(c.start_year)||
25 decode(c.end_year,c.start_year,'','-'||to_char(c.end_year))
26 )
27 pivot (sum(netloan) for project_sector_id in (1,2,3,4,5,6,7,8))
28 order by years
29 /
YEARS 1 2 3 4 5 6 7 8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1975-1979 23.64 .55 7 20.96 30.91
1980-1984 6 9.31 3.79 7.5 12.1
1985-1989 12.58 11.64 1.64
1990-1994 55.12 21.76 38.61 26.79 8
1995-1999 15.37 31.74 52.42
2000-2004 115.8 12 8 26.15 3
2005-2009
2010-2014 3.5
2015
2016
2017 15
2018
2019
2020
|
|
|
|
|
Re: Group pivot column [message #682094 is a reply to message #682092] |
Mon, 05 October 2020 01:46 |
|
Michel Cadot
Messages: 68669 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The queries in my last 2 posts are the same one for the 2 set of data you posted.
I generate a calendar to have all intervals and years from the first year in your data to current year even if you have no data for this interval or year.
|
|
|
Goto Forum:
Current Time: Sat Jul 27 09:35:02 CDT 2024
|