← Back to Kurakani

SQL - Help please

Original Post

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

NepaliBhai
May 31, 2007 12:12 PM · 11,804 views
17 Replies
NIRAVANA75062 May 31, 2007 12:35 PM
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 May 31, 2007 12:48 PM
I trying to concatinate three fields if name, address and state match.
NIRAVANA75062 May 31, 2007 12:54 PM
name matches between rows of same table or u are comparing the name with target table.
NIRAVANA75062 May 31, 2007 12:59 PM
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 May 31, 2007 1:01 PM
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 May 31, 2007 1:03 PM
Please go through my sql which creats temporary table and result. You can add on that sql. I will try with my data.
NepaliBhai May 31, 2007 1:27 PM
Khoi kasaile help garena !!
sujanks May 31, 2007 2:27 PM
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 May 31, 2007 2:36 PM
you can get more help here: - http://forums.mysql.com/index....
November May 31, 2007 2:38 PM
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 May 31, 2007 2:41 PM
- sujanks, Yes, you are right. Give me your shot. It will be great.
NIRAVANA75062 May 31, 2007 2:54 PM
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 May 31, 2007 2:58 PM
- November Jee, Use rtrim. select rtrim(field1),rtrim(field2) from table1
NIRAVANA75062 May 31, 2007 3:01 PM
why dont u try this selet rtim(ltrim(field)) from table 1
November May 31, 2007 3:03 PM
Thank u nepaliBhai... i appreciate your help...
NIRAVANA75062 May 31, 2007 3:24 PM
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 May 31, 2007 4:25 PM
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.
Login to join the conversation.

You might be interested in...

Latest Kurakani View all
Page 1 of 30
Recent Classifieds View all
Page 1 of 33
Upcoming Events View all
Page 1 of 2
Service Providers View all
Page 1 of 23
Ask Sajha AI Assistant