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

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.

I trying to concatinate three fields if name, address and state match.

name matches between rows of same table or u are comparing the name with target table.

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.

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.

Please go through my sql which creats temporary table and result. You can add on that sql. I will try with my data.

Khoi kasaile help garena !!

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?

you can get more help here:

- http://forums.mysql.com/index.php

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.

- sujanks,
Yes, you are right. Give me your shot. It will be great.

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.

- November Jee,

Use rtrim.
select rtrim(field1),rtrim(field2) from table1

why dont u try this
selet
rtim(ltrim(field))
from
table 1

Thank u nepaliBhai... i appreciate your help...

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

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.