How can I add Number at the end?
-
Hi, Gurus.
I have a q for U.
I have been fighting with this for a while.
So I need some help.
Here is some data..
-----------------------------
ID Code SomethingIWant
1 A A01
2 A A02
4 B B01
23 C C01
34 C C02
25 C C03
89 D D01
-------------------------------
ID column is random but unique.
SomethingIWant column is the one I want to generate.
What I have been doing is using a cursor(Fetch Next). Check to next Code and if that code is the same as before, I want to add "01" at the end. Next one goes "02", if not start with "01"..However that doesnt quite work for me right now..
Any other idea?
Thx in advance.
Jay
-
Create Table Test(id int, Code char(1), SomethingIWant Varchar(10))
GO
Insert test values(1,'A','')
Insert test values(2,'A','')
Insert test values(4,'B','')
Insert test values(23,'C','')
Insert test values(34,'C','')
Insert test values(25,'C','')
Insert test values(89,'D','')
GO
Update t1
Set SomethingIWant=
Case When IsNull((Select count(*)
from test as t2
where t2.[id]<t1.[id] And
t2.code=t1.code),0)<=0 Then t1.Code+'01'
Else t1.Code+Replace(Right(' '+Cast((Select count(*)
from test as t3
where t3.[id]<t1.[id] And
t3.code=t1.code)+1 as VarChar(2)),2),' ','0') End
from Test as t1
GO
Select * from Test Order by Code,[id]
go
Drop table test
go
-
Here's an example that you can paste into SQL query analyzer. I use this all the time, and have gotten away from the traditional cursors. Haven't ever used it on a really big recordset, I bet it would be really slow!
--Create a temp table with some test values...
CREATE TABLE #test (ID INT, letr varchar(1))
INSERT INTO #test (ID, letr) VALUES(1, 'A' )
INSERT INTO #test (ID, letr) VALUES(2, 'A' )
INSERT INTO #test (ID, letr) VALUES(4, 'B' )
INSERT INTO #test (ID, letr) VALUES(23, 'C' )
INSERT INTO #test (ID, letr) VALUES(24, 'C' )
INSERT INTO #test (ID, letr) VALUES(25, 'C' )
INSERT INTO #test (ID, letr) VALUES(89, 'D' )
--Create another table with the new column, copy data to it...
--Here is where you could create another field such as ID2 INT IDENTITY to
--base the 'looping' on.
CREATE TABLE #results (ID INT, letr Varchar(1), siw Varchar(3))
INSERT INTO #results (ID, letr)
SELECT * FROM #test
--Look at data before processing
SELECT * FROM #results
--Set up variables
DECLARE @ctr INT
DECLARE @valctr INT
DECLARE @max INT
DECLARE @siw VarChar(3)
DECLARE @curletr Varchar(1)
DECLARE @prvletr Varchar(1)
SET @ctr = (SELECT MIN(ID) FROM #results)
SET @max = (SELECT MAX(ID) FROM #results)
SET @prvletr = ''
--Go row by row. Note that if your recordset doesn't have
WHILE @ctr <= @MAX
BEGIN
SET @curletr = (SELECT letr FROM #results WHERE ID = @ctr)
IF @curletr IS NOT NULL
BEGIN
IF @curletr = @prvletr
SET @valctr = @valctr + 1
ELSE
SET @valctr = 1
SET @siw = @curletr + CASE When LEN(@valctr) = 1 then '0' ELSE '' END + CAST(@valctr AS VARCHAR(2))
UPDATE #results SET siw = @siw WHERE ID = @ctr
SET @prvletr = @curletr
END
SET @ctr = @ctr + 1
END
--Look at the results:
SELECT * FROM #results
--Cleanup
DROP TABLE #resultsIf there are lots of gaps in your ID field, or it's not numeric, you can create another field as a counter such as ID2 INT IDENTITY, and base the looping on that field.
Hope it helps!
-
I'm guessing that Len's (5409045121009) solution would be better than mine since it's a set-based solution wheras mine uses a row by row approach. It would be interesting to see how these would compare on a really large recordset...
-
One more solution using derived table
update t1
set somethingiwant = t2.somethingiwant
from test as t1
join(SELECTid,
code+CAST((select count(*)
from test b
where a.code = b.code
and a.id >=b.id)
as varchar(10)) as somethingiwant
from test a
)as t2
on t1.id = t2.id
-
BTW, I was using 5409045121009 table structure. Also I didnt put in the leading zeros but that would be easy enough if you need it.
-
Try to improve the update a bit.
Update t1
Set SomethingIWant=
Case When Exists(Select '1' from test as t2 where t2.[id]<t1.[id] And t2.code=t1.code) Then
t1.Code+Right('0'+Cast((Select count(*) from test as t3 where t3.[id]<t1.[id] And t3.code=t1.code)+1 as VarChar(2)),2)
Else t1.Code+'01' End
from Test as t1
-
Hi, Guys..
U guys are really GURUS.....
I have huge data set.. I will test both ways and I will let U know..
And I have a q for 5409045121009.
Would U explain this logic a little bit..
Select '1' from test as t2 where t2.[id]<t1.[id] And t2.code=t1.code
And I have a q for GregLyon.
Would U explain this logic a little bit..
SET @siw = @curletr + CASE When LEN(@valctr) = 1 then '0' ELSE '' END + CAST(@valctr AS VARCHAR(2))
Thx
Jay
-
Rheejay, if one look at the statement with the Exists in mind, I am trying to determine 'are there any previous entries for the same code'. If not then this is the start of a 'run' else go and count how many are before this one.
Select '1' from test as t2 where t2.[id]<t1.[id] And t2.code=t1.code
-
Hi Rheejay,
SET @siw = @curletr + CASE When LEN(@valctr) = 1 then '0' ELSE '' END + CAST(@valctr AS VARCHAR(2))
This line of SQL starts with your letter and concatenates a number to it. The CASE statement just adds a leading 0 if the @valctr variable is between 1 and 9, so you end up with A01, A02, A03 instead of A1, A2, A3. The CAST part makes sure that SQL doesn't try to treat the value as a number and attempt to do addition instead of concatenation.
-
Thx All..
This makes whole lot sense
Jay
-
Oh..Man..I forgot something to post..
I have 4500 records to test the both of ways.
-GregLyon's script ran for 40 mins but did not finish to pull.
-5409045121009's script ran less than a 2 seconds to update column.
I think, for the performance purpose, 5409045121009's script is faster.
However GregLyon's script gave me lots of thoughts regarding cursor so I think I can apply this logic to other scripts.
Thx for all..
Jay
-
CREATE TABLE #test (ID INT, letr varchar(1))
INSERT INTO #test (ID, letr) VALUES(1, 'A' )
INSERT INTO #test (ID, letr) VALUES(2, 'A' )
INSERT INTO #test (ID, letr) VALUES(4, 'B' )
INSERT INTO #test (ID, letr) VALUES(23, 'C' )
INSERT INTO #test (ID, letr) VALUES(24, 'C' )
INSERT INTO #test (ID, letr) VALUES(25, 'C' )
INSERT INTO #test (ID, letr) VALUES(89, 'D' )
-- or try this
select a.ID, max(a.letr) letr, max(a.letr)+ substring(convert(char, count(*)+100),2,2)SomethingUWant
from #test a, #test b
where b.ID<=a.id and b.letr=a.letr
group by a.ID
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply