Need help in teradata to delete duplicate values after concatinating 2 field values.

Database

Need help in teradata to delete duplicate values after concatinating 2 field values.

I am new to Teradata.

Below is the test data of Test1 table

RnoNameAddressActive
111KrishDelhi 
111RoshanIndia 
123SwethaISOLA 
123NHyderabad 
123MTelangana 
123GopiIndia 
333AnilP Nagar 
333KumarPanjab 
333LoatIndia 
444AravindVizag 
444RIndia 

 

My requirement is I have to concatinate Name and Address fields for the same Rno, below should be my final data in the table

 

RnoNameAddressActive
111Krish, RoshanDelhi, IndiaY
111RoshanIndiaN
123Swetha, N, M, GopiISOLA, Hyderabad, Telangana, IndiaY
123NHyderabadN
123MTelanganaN
123GopiIndiaN
333Anil, Kumar, LoatP Nagar, Panjab, IndiaY
333KumarPanjabN
333LoatIndiaN
444Aravind, RVizag, IndiaY
444RIndiaN

 

so that later I can delete all Active = 'N' values from the Test1 table.

 

Can some one provide me a stored procedure to have above data in my table.

2 REPLIES
N/A

Re: Need help in teradata to delete duplicate values after concatinating 2 field values.

You need to provide additional information to do so - something which is allowing you to order the rows in the correct way.

The data in the DB is not sorted.

An option could be to sort by name and address but this would result in different ordering for the the Active = 'Y'  row...

Beside this check the with recursivce option in Teradata

Teradata Employee

Re: Need help in teradata to delete duplicate values after concatinating 2 field values.

Also, it would be far, far more efficient to insert these values you really want into a new table, drop the old table and then rename the new table, than it would be to add this flag to an existing table, populate the flag column, and then s-l-o-w-l-y delete most of the rows.  In fact, for something like this you might be better off exporting the table in the required sequence, creating a new flat file from this using C/Java/awk/perl/python and then reloading the table.