I am new to Teradata.
Below is the test data of Test1 table
My requirement is I have to concatinate Name and Address fields for the same Rno, below should be my final data in the table
|111||Krish, Roshan||Delhi, India||Y|
|123||Swetha, N, M, Gopi||ISOLA, Hyderabad, Telangana, India||Y|
|333||Anil, Kumar, Loat||P Nagar, Panjab, India||Y|
|444||Aravind, R||Vizag, India||Y|
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.
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
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.