Need an output like inverse group by

General
Highlighted
Enthusiast

Need an output like inverse group by

Hi,

 

Anyone having any idea on how to achieve the below output:

====== TABLE =======
Col1          Col2 
A                3
B                2
D                5
X                1


Desired OUTPUT:

A
A
A
B
B
D
D
D
D
D
X


I am using TD version 16

Thanks in Advance
Supratim


Accepted Solutions
Teradata Employee

Re: Need an output like inverse group by

Hi Supratim,

 

We can also use EXPAND ON clause :

 

create multiset volatile table mvt_datas, no log
( col1 		char(1)
, col2		byteint
)
primary index (col1)
on commit preserve rows;

insert into mvt_datas values ('A', 3);
insert into mvt_datas values ('B', 2);
insert into mvt_datas values ('D', 5);
insert into mvt_datas values ('X', 1);

  select col1
    from mvt_datas
  expand on period(current_date, current_date + col2) as expd by interval '1' day
order by col1 asc; col1 ---- A A A B B D D D D D X

 

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Teradata Employee

Re: Need an output like inverse group by

Hi.

 

You can easely iterate using CALENDAR with something like:

 

SELECT a.Col1
FROM TheTable a,
SYS_CALENDAR.CALENDAR b
WHERE b.day_of_calendar <= a.Col2

ORDER BY 1

 

 

HTH.

 

Cheers.

 

Carlos.

Teradata Employee

Re: Need an output like inverse group by

Hi Supratim,

 

We can also use EXPAND ON clause :

 

create multiset volatile table mvt_datas, no log
( col1 		char(1)
, col2		byteint
)
primary index (col1)
on commit preserve rows;

insert into mvt_datas values ('A', 3);
insert into mvt_datas values ('B', 2);
insert into mvt_datas values ('D', 5);
insert into mvt_datas values ('X', 1);

  select col1
    from mvt_datas
  expand on period(current_date, current_date + col2) as expd by interval '1' day
order by col1 asc; col1 ---- A A A B B D D D D D X

 

 

Enthusiast

Re: Need an output like inverse group by

Hey Walder,

It works perfectly. However, I am not very clear about the logic.
Could you please explain the same.

 

Many thanks!

Supratim

Enthusiast

Re: Need an output like inverse group by

Carlos,

 

Yes it works, but when I checked for the max(day_of_calendar) it is 73414.

So if I have any row with a count of a value more than 73414 I think it will not be working.

Regards

Supratim 

Teradata Employee

Re: Need an output like inverse group by

EXPAND ON is a Teradata specific extention to SQL:2011.

16.20 documentation :

https://docs.teradata.com/reader/b8dd8xEYJnxfsq4uFRrHQQ/nG7ALT2MzFJkVlRvKhkydw

 

It generate rows according to a period datatype over a specified interval.

As you don't have a period datatype, I'm using using the generated current_date and current_date + col2 range.

 

See it as this :

select col1, period(current_date, current_date + col2) as pd1
  from mvt_datas;

 col1 pd1                      
 ---- ------------------------ 
 A    (2019-03-04, 2019-03-07)
 D    (2019-03-04, 2019-03-09)
 X    (2019-03-04, 2019-03-05)
 B    (2019-03-04, 2019-03-06)

Expand will generate a new period datatype, which is thy one "splitted" by the specified interval :

  select col1
       , period(current_date, current_date + col2) as pd1
       , expd
    from mvt_datas
  expand on pd1 as expd by interval '1' day
order by col1 asc;

 col1 pd1                      expd                     
 ---- ------------------------ ------------------------ 
 A    (2019-03-04, 2019-03-07) (2019-03-04, 2019-03-05)
 A    (2019-03-04, 2019-03-07) (2019-03-05, 2019-03-06)
 A    (2019-03-04, 2019-03-07) (2019-03-06, 2019-03-07)
 B    (2019-03-04, 2019-03-06) (2019-03-04, 2019-03-05)
 B    (2019-03-04, 2019-03-06) (2019-03-05, 2019-03-06)
 D    (2019-03-04, 2019-03-09) (2019-03-04, 2019-03-05)
 D    (2019-03-04, 2019-03-09) (2019-03-05, 2019-03-06)
 D    (2019-03-04, 2019-03-09) (2019-03-06, 2019-03-07)
 D    (2019-03-04, 2019-03-09) (2019-03-07, 2019-03-08)
 D    (2019-03-04, 2019-03-09) (2019-03-08, 2019-03-09)
 X    (2019-03-04, 2019-03-05) (2019-03-04, 2019-03-05)

As for your case, you don't need any of the period informations, I've narrowed it to usefull columns only.

 

 

Enthusiast

Re: Need an output like inverse group by

Awesome! Thanks a lot Walder :)