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
- 4 Answers
- 7807 Views
- Bank Of America, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
How to delete duplicate record in Informatica?
If I have router with 10 records, if the condition doesn’t satisfy the records will pass to the default group,then where can we see that records and where that records will store?
What does reusable transformation mean?
what is the difference between informatica6.1 and infomatica7.1
How to generate the HTML output using Informatica.
How to eliminate duplicates from flat file..what is the procedure
Whats the difference between informatica powercenter server, repositoryserver and repository?
How do you load only null records into target? Explain through mapping flow.
What is the difference between Junk and Confirmed Dimention? where can be used htat one in Informatica?
Briefly explain the aggregator transformation?
What are the new features in Informatica 5.0?
What is log file,and where is it located,why u r using the log file
Teradata 
