how to remove duplicate?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008

Hi i am new to sql sever 2008 r2.Actuvally i am use CTE to remove the Duplicate data. so,
i wrote query like this given below

SQL

 ;WITH CTE(NAME_TYPE,SCORE,PERCENTAGE,SCORE1,PERCENTAGE1,SCORE2,PERCENTAGE2,NAME_TYPE )
  AS
  (
  SELECT NAME_TYPE,SCORE,PERCENTAGE,SCORE1,PERCENTAGE,SCORE2,PERCENTAGE,ROW_NUMBER()over(PARTITION BY  NAME_TYPE ORDER BY NAME_TYPE )  FROM INDIVIDUAL
  )
SELECT * FROM CTE

o/p:

NAME_TYPE   SCORE PERCENTAGE SCORE1 PERCENTAGE1 SCORE2 PERCENTAGE2
RAM           45    5%             
RAHUL         47    3%
RAHUL                         30          5%                  
RAHUL                                             45      7%
RAGU                          60         10% 
RAGU                                              70       2%

i have to remove the duplicate in name_type column help me..

i am having table like this

id   nametype  score           date
1      Ram      1             07-06-2013
1      Rahul     2            07-06-2013
1      Rahul     3             05-05-2012
1       Rahul    4              06-5-2011
1       Ragu      6            07-4-2012
1        Ragu      7            07-04-2011

o/p;

Name-type     2013-2012     2012-2011   2011-2010
  Ram           12 3%
  Rahul         11 10%       4 5%
  Ragu                                      13 6%

half of i completed i done daterange i calicated total score and percentage .but i am geting o/p like this

name_type      2013-2012      2012-2011  2011-2010
Ram              12 3$
Rahul            11 10%
Rahul                            4 5%
Ragu                                        13 6%

i want to remove dupilctaes in name_type coloumn.how ? please help me

Posted 11-Aug-13 23:55pm

Member 9846414

Updated 14-Aug-13 10:04am

Maciej Los

v5

Add a Solution

Comments

Maciej Los 12-Aug-13 7:10am

Not clear... ;(
Please, be more specific and provide more details. Based on which condition do you want to remove duplicates? What input data?

Member 9846414 14-Aug-13 14:40pm

i clearly mention above .i am get o/p like that.so plz help me .to remove dulpicate in that particular coloumn .how?

Maciej Los 14-Aug-13 16:24pm

Still nothing useful.

Member 9846414 15-Aug-13 8:24am

http://www.codeproject.com/Questions/637665/how-to-remove-duplicate

please see this link .

3 solutions

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)

M.R.A. · Answer 1 · 2013-08-12T02:30:00

Solution 1

use distinct keyword to remove duplicate.

Posted 12-Aug-13 2:30am

M.R.A.

Comments

Member 9846414 14-Aug-13 14:42pm

i tried .but it not working .can u see once i updated it.wht o/p i want...

Member 9846414 15-Aug-13 8:32am

http://www.codeproject.com/Questions/637665/how-to-remove-duplicate

please see this link help me

Corporal Agarn · Answer 2 · 2013-08-14T09:27:00

Solution 2

Create a temp table then populate the unique "NAME_TYPE" column. Then populate the remaining columns.

Posted 14-Aug-13 9:27am

Corporal Agarn

Comments

Member 9846414 15-Aug-13 8:24am

http://www.codeproject.com/Questions/637665/how-to-remove-duplicate

please see this link

Maciej Los · Answer 3 · 2013-08-15T20:42:00

Try this:

SQL

DECLARE @tmp TABLE (id INT, [name] VARCHAR(30), [year1] VARCHAR(30), score1 INT, percentage1 DECIMAL(2,2), [year2] VARCHAR(30), score2 INT, percentage2 DECIMAL(2,2), [year3] VARCHAR(30), score3 INT, percentage3 DECIMAL(2,2))


INSERT INTO @tmp (id, [name], [year1], [score1], [percentage1], [year2], [score2], [percentage2], [year3], [score3], [percentage3])
SELECT 1, 'Ram', '2012-2013', 50, .04, null, null, null, null, null, null
UNION ALL SELECT 1, 'Ragu', '2013-2011', 60, .07, null, null, null, null, null, null
UNION ALL SELECT 1, 'Ragu', null, null, null, '2011-2012', 40, .06, null, null, null
UNION ALL SELECT 1, 'robet', null, null, null, '2011-2012', 59, .03, null, null, null
UNION ALL SELECT 1, 'Ragu', null, null, null, null, null, null, '2010-2011', 30, .02
UNION ALL SELECT 1, 'robet', null, null, null, null, null, null, '2010-2011', 53, .07

DROP TABLE #tt
CREATE TABLE #tt (id INT, [sc_turn] INT, [name] VARCHAR(30), [year] VARCHAR(30), [score] INT, [percentage] DECIMAL(2,2))

INSERT INTO #tt (id, [sc_turn], [name], [year], [score], [percentage])
SELECT id, [sc_turn], [name], [year], [score], [percentage]
FROM (
	SELECT id, 1 AS [sc_turn], [name], [year1] AS [year], [score1] AS [score], [percentage1] AS [percentage]
	FROM @tmp
	UNION ALL 
	SELECT id, 2, [name], [year2], [score2], [percentage2]
	FROM @tmp
	UNION ALL
	SELECT id, 3, [name], [year3], [score3], [percentage3]
	FROM @tmp
) AS T
WHERE [year] IS NOT NULL AND [score] IS NOT NULL AND percentage IS NOT NULL

SELECT *
FROM #tt

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)

SET @cols = STUFF((SELECT DISTINCT '],[' + [year] 
				FROM #tt
				ORDER BY '],[' + [year]
			FOR XML PATH('')),1,2,'') + ']'
--SELECT @cols

SET @dt = N'SELECT [name], [year], CONVERT(VARCHAR(30), [score]) + '' '' + CONVERT(VARCHAR(30), [percentage]*100) + ''%'' AS [rating]
			FROM #tt'
SET @pt = N'SELECT [name], ' + @cols + ' ' +
			'FROM (' + @dt + ') AS DT ' +
			'PIVOT(MAX([rating]) FOR [year] IN(' + @cols + ')) AS PT' 
EXEC(@pt)

DROP TABLE #tt

Result:

        [2010-2011]     [2011-2012]     [2012-2013]	[2013-2011]
Ragu	30 2.00%	40 6.00%	NULL		60 7.00%
Ram	NULL		NULL		50 4.00%	NULL
robet	53 7.00%	59 3.00%	NULL		NULL

how to remove duplicate?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us