[Show all top banners]

NepaliBhai
Replies to this thread:

More by NepaliBhai
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 SQL - Help please
[VIEWED 5732 TIMES]
SAVE! for ease of future access.
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

 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
Posted on 05-31-07 1:27 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Khoi kasaile help garena !!
 
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?
 
Posted on 05-31-07 2:36 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 
 
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.
 
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.
 
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.
 
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
 
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
 
Posted on 05-31-07 3:03 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Thank u nepaliBhai... i appreciate your help...
 
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
 
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.
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 7 days
Recommended Popular Threads Controvertial Threads
TPS Re-registration case still pending ..
To Sajha admin
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters