[VIEWED 5732
TIMES]
|
SAVE! for ease of future access.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 12:12
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
I have a sql question. Please take a look at the table image. I need to have result table with additional field that has concatinated value of Extra1, Extra2, Extra3 when name, address and state match. I have written this way but did not get correct result. select * from testtable order by name drop table #table1 SELECT *, case when (rtrim(Extra1) is not null and rtrim(Extra1) <>'') then rtrim(Extra1) else '' end + case when (rtrim(Extra2) is not null and rtrim(Extra2) <>'') then ',' + rtrim(Extra2) else '' end + case when (rtrim(Extra3) is not null and rtrim(Extra3) <>'') then ','+ rtrim(Extra3) else '' end as Concatinated into #table1 FROM testtable y select * from #table1 order by name update #table1 set Concatinated = rtrim(y.Extra1) + ',' + rtrim(y.Extra2) + ',' + rtrim(y.Extra3) from testtable y, #table1 x where rtrim(y.name) = rtrim(x.name) and rtrim(y.address) = rtrim(x.address) and rtrim(y.state) = rtrim(x.state) select * from #table1 order by name
|
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 12:35
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
are u trying to match name, address and state from source table to target table or u are just comparing it with other rows of source itself.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 12:48
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
I trying to concatinate three fields if name, address and state match.
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 12:54
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
name matches between rows of same table or u are comparing the name with target table.
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 12:59
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
u can create a new column where u can concatenate extra 1, extra 2 and extra 3. since name and address all will have lower hierarchy, they will stay on new filed grouping level.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 1:01
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
There is only one physical table. There is no target physical table.You have to put result in temporary table first and then scan through the main physical table againts the temporary table. Then you will get result which is target result.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 1:03
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Please go through my sql which creats temporary table and result. You can add on that sql. I will try with my data.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 1:27
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Khoi kasaile help garena !!
|
|
|
sujanks
Please log in to subscribe to sujanks's postings.
Posted on 05-31-07 2:27
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
let me get this.
for example, there are three rows for 'ram living in NY, NY'.
so, u want all three rows still intact, and have all the distinct information from extra1, extra2 and extra3 received from all three rows be concatenated but separated by comma, and then store that value in concatenate col in all the three rows?
what programming language u are trying to use, or u wanted to just use sql?
|
|
|
sujanks
Please log in to subscribe to sujanks's postings.
Posted on 05-31-07 2:36
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
|
|
|
November
Please log in to subscribe to November's postings.
Posted on 05-31-07 2:38
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
i am sorry but i need another pl/sql help from you guys.. sorry nepalibhai...
is there any way to trim the all column data in one statement. for example i have 20,000 data in a column and the values are using up my bytes. i need to trim all the whitespaces after the values.
my column datatype is char(65) some values are above 50bytes. the actual values are no more than 40bytes but it counts the whitespaces after the data so some are above 50bytes which i need to trim.
i used the syntax,
select trim ( both ' ' from column_name) from table_name
which of course didnt work..tried looking over the iternet.. no help there or at least i didnt type the right words...
please guys... if you could help me on this.. i would really really appreciate it.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 2:41
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
- sujanks,
Yes, you are right. Give me your shot. It will be great.
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 2:54
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
if u only need result set can u try this .
select
name,
address,
state,
Phone,
(case when (rtrim(Extra1) is not null and rtrim(Extra1) <>'') then rtrim(Extra1) else '' end +
case when (rtrim(Extra2) is not null and rtrim(Extra2) <>'') then ',' + rtrim(Extra2) else '' end +
case when (rtrim(Extra3) is not null and rtrim(Extra3) <>'') then ','+ rtrim(Extra3) else '' end ) as Concatinated
FROM testtable
I might be wrong about this query but i think u can apply same kind of logic.
if u need to create a new target table, then create a store proc which will create new table and insert all those values into that table.
|
|
|
NepaliBhai
Please log in to subscribe to NepaliBhai's postings.
Posted on 05-31-07 2:58
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
- November Jee,
Use rtrim.
select rtrim(field1),rtrim(field2) from table1
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 3:01
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
why dont u try this
selet
rtim(ltrim(field))
from
table 1
|
|
|
November
Please log in to subscribe to November's postings.
Posted on 05-31-07 3:03
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Thank u nepaliBhai... i appreciate your help...
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 3:24
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
create table target_table
(
name varchar(20),
address varchar(30),
state varchar (2),
Phone varchar(12),
concatinated varhcar(20))
insert into target_Table
(select
name,
address,
state,
Phone,
(case when (rtrim(Extra1) is not null and rtrim(Extra1) <>'') then rtrim(Extra1) else '' end +
case when (rtrim(Extra2) is not null and rtrim(Extra2) <>'') then ',' + rtrim(Extra2) else '' end +
case when (rtrim(Extra3) is not null and rtrim(Extra3) <>'') then ','+ rtrim(Extra3) else '' end ) as Concatinated
|
|
|
NIRAVANA75062
Please log in to subscribe to NIRAVANA75062's postings.
Posted on 05-31-07 4:25
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
above query didnt work. nepali bhai u might have to right two or three sub queries and group the lowest level where u create a concatenate.
|
|