SQL query with repetition of values

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

SQL query with repetition of values

Hi Experts,

 

I have a table with input as :

ID    Name

1       A

2       A

3       C

4       D

4       A

..........

So the logic should be if ID=1 then name should be only once appearing as in it should be A

if ID =2 then ouput should be ID=2 And Name= AA

if ID =3 then output should be ID=3 And Name=CCC

if ID=4 then output should be ID=4 And Name=DDDD ,

 ID=4 And Name=AAAA

 

PS: a generic answer is needed without CASE preferbly as ID may have numbers from 1 to 1500

 

Thanks in Advance

4 REPLIES
Teradata Employee

Re: SQL query with repetition of values

Hi Swapnanil,

 

Using RPAD function then it's easy to do :

create multiset volatile table mvt_repetition, no log
( id    byteint
, name  char(1)
)
primary index (id)
on commit preserve rows;

insert into mvt_repetition (id, name) values (1, 'A');
insert into mvt_repetition (id, name) values (2, 'A');
insert into mvt_repetition (id, name) values (3, 'C');
insert into mvt_repetition (id, name) values (4, 'D');
insert into mvt_repetition (id, name) values (4, 'A');

select id
     , name
     , rpad(name, id, name) (varchar(10)) as output
  from mvt_repetition;

1	A	A
2	A	AA
3	C	CCC
4	D	DDDD
4	A	AAAA
Enthusiast

Re: SQL query with repetition of values

Thanks Waldar.

That's something new I learnt today -:)

Senior Apprentice

Re: SQL query with repetition of values

Hi,

 

No purely SQL solution comes to mind if not using CASE (and I understand why you don't want that).

 

I think you'll need a C or Java UDF which takes in two parameters (ID and Name) and returns the required string. (and 'No', my C and Java are not up to this... although I suspect it is not that difficult).

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: SQL query with repetition of values

Very smart! I like that.

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com