Hello,

Could any one please help in trying to find a solution for the problem below.
There are the columns COL1,COL2,COL3,DESC
After concatenating all the fields below, i should be able to avoid redundant data or remove duplicate data that is concatenated from COL1,COL2,COL3,DESC.
The whole idea is to get a unique string that should contain the data from all the columns after concatenation
COL1||COL2||COL3||DESC

COL1 COL2 COL3 DESC
MAZDA XYZ COMM MAZDA XYZ AWD COMM WITH UPGRADE
BMW 3.25 9iJKpon BMW 325i REG 9iJKpon


What i am looking for is a single string containing the unique values after concatenating COL1||COL2||COL3||DESC
MAZDA XYZ AWD COMM WITH UPGRADE
BMW 3.25 325I REG 9iJKpon

Thanks for ur help in advance..

Regards,
--KA

If I didn't misunderstand your requirements,
here is a hint showing steps with reference documents to solve the problem.

1) UNPIVOT
unpivot col1, col2 and col3 to col_x
http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

2) SELECT DISTINCT col_x ...
select-clause in
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows


3) concatenate strings in a column of multiple rows
LISTAGG function.
Combine values from different records using comma

If you want to remove redundant data in col1, col2, col3
and to remove redundant data in desc,
then use step 1) and step 3) in my previous post.
In the reference thread in step 3),
I showed also techniques to remove redundant data from each columns(basic_skill2 and basic_skill1 in the examples).

select col1||col2||col3||desc as concatenated_columns
from ?
group by col1||col2||col3||desc

Thanks for the response tonkuma and Dick Brenholtz

I have edited the requirement with example, will try the solutions u have suggested in the mean while..


Thanks,
KA

There may be
1) same data in differnt columns in a row.
2) same data in different row of same column or different columns.

For example:
With this sample data.
your query's result duplicates 'abc', 'mno' etc.

yeah, the permutations will exist
but, the TS said, and I qoute:'abc' and 'cba' are unique strings.
the GROUP BY satisfies that requirement.

but if order is not important,
then yes your examples are the solution,
since the TS obviously does not want to clean-up his table.

I am i missing anything..
since the query is still showing me the duplicate results in the same column.

here i am trying with 2 columns..


col1 col2
ABC ABC123
BCD BCD 1234

SELECT COL1|| ' '||COL2 from table
group by COL1|| ' '||COL2

Result
ABC ABC123
BCD BCD 1234

Where are the boundaries of each columns?

If you want to remove redundant words(and not redundant column values),
I think you must separate words in each column(or each strings of concatenated columns).

This needs additional technique.

I don't have control on the data that is coming from legacy systems..
But once in my staging i can manipulate the data..

Below is the sample data..

col1
MAZDA
BMW

****
col2
XYZ
3.25

********
col3
COMM*
REG 9iJKpon

*******
desc
MAZDA XYZ AWD COMM WITH UPGRADE
BMW 325i 9iJKpon

karv,

you can not use the concatenation solution that I provided.
and I don't think anything will remove one of these: '3.25' '325i'

karv,

please provide enough and complete sample data,
if you want to receive significant and/or usefull responses.

Your provided data were vague and not complete for me.
For example:
1) How to make this intermediate data from bare data?
(How to know each of MAZDAs make one concatenated string and BMWs make another concatenated string?
(Do you want to make a string from rows with same values of COL1?)
or, concatenate columns in a row only and your data were these kind?
2) did some words in desc repeat data in col1, col2, col3?
3) If sequence of words were significant, how to get this result
from this data?
If I removed second and subsequent repeated words from left to right,
the result may be
BMW 3.25 9iJKpon 325i REG

Hello tonkuma,
I have attached a sample set of data from the Source.
Hope this helps..
Please let me know if you need more information.

Thanks to all the members who have helped so far!!


--KARV

I couldn't see the data in your file.
My unzip program recovered your file to Book1.xlsx.