Fetching second and consecutive duplicate values as NULL in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Dear All,

I am trying to fetch second and consecutive duplicate values as NULL by joining two tables based on Order ID which is common in both the tables.

I have tried using CTE and many other queries but not getting accurate result.

Please check the below URL to see the result :

http://www.ikatraining.com/sqlquery.png

I need same number of records but want to return NULL value for the duplicate values for OrderXML column (highlighted in the screen shot) in the final output of my select query.

Any help or suggestion would be greatly appreciated.

Thanks & Regards,

What I have tried:

CTE used in SQL Query and UNION Also.. I have given the link for the result I am getting

Posted 12-Aug-18 23:00pm

Raj.rcr

Updated 13-Aug-18 1:37am

Add a Solution

Comments

CHill60 13-Aug-18 7:08am

Please do not include links to images to explain your problem. You could have easily typed that information into your question and your link makes your post look like spam.
You say you have tried using a CTE and a UNION - then show the code that you have tried

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-08-13T01:37:00

Here is a demonstration of the principle, you'll have to adapt it to your own table

SQL

-- Some sample data
create table #demo (test varchar(10), myValue varchar(10))
insert into #demo (test, myValue) values
('val1', 'value 1'),
('val1', 'value 1'),
('val2', 'value 1'),
('val2', 'value 2'),
('val2', 'value 2')

The query:

SQL

;with cte as 
(
	select test, myValue, 
		row_number() OVER (PARTITION BY test ORDER BY test, myValue) as rn
	from #demo
)
select test, case when rn = 1 then myValue else null end as myValue
from cte

Comment:
Because of the partition on ROW_NUMBER [^] we are getting a row number for each row within a test value. That is how you can detect the "duplicates".

Take care on how you choose to order the values as it will determine which are deemed to be duplicates.