May 18, 2011 at 12:33 pm
Here is the situation and I need help,
Source data like this
INVOICE
C-125
A-567
C-125
Here what I want
INVOICE
C-125
A-567
C-125-A
Note:- I can’t do manually because I have 25 thousand duplicate that I want to fix. Thanks for help.
May 18, 2011 at 12:43 pm
You want to affix an "A" to the end of a duplicate, right? That's easy enough.
What do you want to do if there's more than 2 of a single value? Add "B", and "C", and so on? Add "AA" to the third, "AAA" to the fourth? "A1", "A2"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 12:46 pm
THANKS FOR YOUR REPLY, I am sorry i didn't understand very well what you trying to say, It would be awesome if you type a sql syntax for me. Appreciate it.
May 18, 2011 at 12:56 pm
I need to know what you want if there are three "C-125"s, for example, before I could write any code. What do you want on the third one, if that happens?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 2:58 pm
if there is third, it should be value-C, if there is fourth, it should be value-D,
and i want to update MY INVOICE FIELD
UPDATE MY TABLE
SET INVOICE = (YOUR CODE)
I really apprecaite your help.
May 18, 2011 at 3:05 pm
DECLARE @test-2 TABLE (RowID INT IDENTITY PRIMARY KEY CLUSTERED, Col1 VARCHAR(20));
INSERT INTO @test-2 (Col1)
VALUES ('INVOICE'),
('C-125'),
('A-567'),
('C-125'),
('B-133'),
('C-125');
WITH cte AS
(
SELECT RowID,
Col1,
RN = ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY RowID)
FROM @test-2
)
SELECT CASE WHEN RN = 1 THEN Col1 ELSE col1 + '-' + CHAR(63+RN) END
FROM cte
ORDER BY RowID;
Which returns:
----------------------
INVOICE
C-125
A-567
C-125-A
B-133
C-125-B
Note that if you get beyond 26 duplicates, you'll start having some funky characters for that suffix.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 18, 2011 at 3:16 pm
Wayn, appreciate your help, quick question
UPDATE MYTABLE
SET INVOICE = WITH cte AS
(
SELECT RowID,
Col1,
RN = ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY RowID)
FROM @test-2
)
SELECT CASE WHEN RN = 1 THEN Col1 ELSE col1 + '-' + CHAR(63+RN) END
FROM cte
ORDER BY RowID;
Note:- i tried to update my field but giving error, could you please gide me what i am doing wrong, Thanks.
May 18, 2011 at 7:06 pm
tooba111 (5/18/2011)
Wayn, appreciate your help, quick questionUPDATE MYTABLE
SET INVOICE = WITH cte AS
(
SELECT RowID,
Col1,
RN = ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY RowID)
FROM @test-2
)
SELECT CASE WHEN RN = 1 THEN Col1 ELSE col1 + '-' + CHAR(63+RN) END
FROM cte
ORDER BY RowID;
Note:- i tried to update my field but giving error, could you please gide me what i am doing wrong, Thanks.
Oh, you want to update data, not just select it (nothing was mentioned about this...)
Well, in that case, then try this:
DECLARE @test-2 TABLE (RowID INT IDENTITY PRIMARY KEY CLUSTERED, Col1 VARCHAR(20), Col2 varchar(20) NULL);
INSERT INTO @test-2 (Col1)
VALUES ('INVOICE'),
('C-125'),
('A-567'),
('C-125'),
('B-133'),
('C-125');
WITH cte AS
(
SELECT RowID,
Col1,
Col2,
RN = ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY RowID)
FROM @test-2
)
UPDATE cte
SET Col2 = CASE WHEN RN = 1 THEN Col1 ELSE col1 + '-' + CHAR(63+RN) END;
SELECT *
FROM @test-2;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply