Hi Friends
I have a sql question, We have the source data as below.
101 address1, address2, address3
and the output shoulb like below
101 address1
101 address2
101 address3
Required a SQL query for this output.
Please let me know if you have any sql query for this.
Thanks in advance.
Hari M
Answers were Sorted based on User's Feedback
Answer / vennela12
Hi Hari.....try this
select 101,address1 from tablet1
union all
select 101,address2 from tablet1
union all
select 101,address from tablet1
By
Vennela
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / ronak
You have to use LPAD function,
Select lpad(address,20,'101')
from tableName;
NOTE: address is column name of address info, 20 is total
characters you want to display including 101 (since in this
case we don't know the characters of address),
Thank you
Ronak
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / ksks
Make use of UNPIVOT to split one record into many rows:
select id,address from T1 unpivot (address for address_type in (address1,address2,address3))
Is This Answer Correct ? | 1 Yes | 0 No |
Hi Vennala
The posted query will work fine. But what if incase 'N'
no.of columns, like address1, address2..., address n.
The query will become lenghty with N no.of Unions.
Is there any script or procedure as alternative??
Please help me out in this regard.
Thanks,
Hari
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / tdguy
Hi Hari,
IF the source data is present in a file, then it can
be handled in the supporting OS say unix or mainframe,
whichever is being used. But the input data is present in a
single column, then i think UDF has to be used.
Is This Answer Correct ? | 0 Yes | 0 No |
Explain the new features of teradata?
Can we have an unconnected lkp to lookup a db2 record against a teradata record? Doesnt seem to work. I could be wrong
What is a dimension table?
What do you mean by teradata intelliflex?
What is the purpose of indexes?
which join mostly use in realtime?
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
How do you check the performance of Teradata Query and list down the basic Performance Tuning steps you use?
Hi Frnds this que is recently asked at IBM there are two rows like aa6588fhfhf,gru282vbvv. Question is howcan we retrive the first number from two rows. Can any one help thanks in advance
Find 2 highest sal from each dept who have completed 5 year in org
can we load 10 millions of records into target table by using tpump?
Frnds.... i have one doubt we have the num like this +913757575......i want to remove 91 and i want lo load remaining data to the target table.. whinch function we can use in teradata????? Plz let me know.. thanks in advance