write s sql query following table some duplicate present i want unique one co

write s sql query following table some duplicate present i
want unique one column duplicate another column display?

name id
a 1
a 1
b 2
b 2
c 3
i want the required output like
unique duplicate
name id name id
a 1 a 1
b 2 b 2
c 3

Question Posted / dinesh

4 Answers
8071 Views
Bank Of America, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

write s sql query following table some duplicate present i want unique one column duplicate anoth..

Answer / ram pothineni

1) In SQ.. override the default query as below..

SELECT NAME,ID,ROW_NUM()OVER(PARTITION BY NAME ORDER BY
NAME)RN FROM TABLE_NAME

2) In router create two groups

UniqueGroup - RN = 1
DuplicateGroup - RN > 1

3) Connect Respective group to respective targets...

-- Ram Pothineni

Is This Answer Correct ?

5 Yes

2 No

write s sql query following table some duplicate present i want unique one column duplicate anoth..

Answer / anto

unique

select distinct name,id from table

duplicate

select name,id from table
group by name,id
having count(name||id) > 1

Is This Answer Correct ?

2 Yes

0 No

write s sql query following table some duplicate present i want unique one column duplicate anoth..

Answer / chandan023

1.Sort the data
2.Expression transformation
a)create a variable1(integer) and var2(string)
b) var2 = concatenate all fields
b) set the value of the variable to 1 if (concat all fields are = var2)
c) create a o/p port = var2
-- output of the expression t/f will be like
NAME ID NEW_COL
a 1 0
a 1 1
b 1 0
b 1 1
c 1 0
3. Use a router transformation and route the data based on the new thirt column.

Happy ETLing :)

Is This Answer Correct ?

0 Yes

0 No

write s sql query following table some duplicate present i want unique one column duplicate anoth..

Answer / senthil

select a.nameid as unique_name_id,b.nameid as DUplicate_name_id
from
(select distinct nameid from name) a
left outer join
(select nameid from name group by nameid having count(*) > 1) b
on a.nameid = b.nameid

Output:
---------
unique_name_id Duplicate_name_id
a 1 a 1
b 2 b 2
c 3

Is This Answer Correct ?

0 Yes

0 No

Post New Answer