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 Go to next message
OraFerro
Messages: 393
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3004
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3004
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3004
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 #682076 is a reply to message #682074] Sun, 04 October 2020 07:08 Go to previous messageGo to next message
OraFerro
Messages: 393
Registered: July 2011
Senior Member
@Michel and @SY
Thanks a lot. Both examples are great.

@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
Re: Group pivot column [message #682077 is a reply to message #682076] Sun, 04 October 2020 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
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. Wink

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 Go to previous messageGo to next message
OraFerro
Messages: 393
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3004
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
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 #682091 is a reply to message #682082] Mon, 05 October 2020 01:13 Go to previous messageGo to next message
OraFerro
Messages: 393
Registered: July 2011
Senior Member
@SY
Quote:

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:
Thanks a lot for you help, in fact I do not want to show 2015-2019 when its only holding the data for 2015. I was trying to comment that the query you sent at that time does that and I want to avoid it.
I also do not need years that do not have data so I removed the union all part:
            union all
             select  to_number(to_char(sysdate,'yyyy')) + 1 - level,
                     1,
                     null
               from  dual
               connect by level <= 6
Thanks again you've been of great help
Re: Group pivot column [message #682092 is a reply to message #682091] Mon, 05 October 2020 01:16 Go to previous messageGo to next message
OraFerro
Messages: 393
Registered: July 2011
Senior Member
@Michel,
Thanks a lot, the query before the last one (the one without the union all) is also exactly what I need.

Ferro
Re: Group pivot column [message #682094 is a reply to message #682092] Mon, 05 October 2020 01:46 Go to previous message
Michel Cadot
Messages: 67538
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.

Previous Topic: update column for the set of records
Next Topic: Pass cursor to Procedure
Goto Forum:
  


Current Time: Mon Nov 23 15:40:12 CST 2020