November 25, 2013 at 3:43 pm
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(2,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (3,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (4,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (5,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (6,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (7,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values(8,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (9,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (10,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (11,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (12,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (13,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (14,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (15,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (16,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (17,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (18,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (19,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (20,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (21,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (22,121)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (23,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (24,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (25,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (26,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (27,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (28,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (29,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (30,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (31,101)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values (32,101)
for the somenum column I need to add alphabets to make them unique. for example 121a,121b,121c....121z,121aa,121ab,101a and so on...
Could you guys please help. I dont know where to start..
November 25, 2013 at 5:19 pm
You mean something like this?:
SELECT UniqueNum
,Somenum
, CHAR(ROW_NUMBER() OVER (PARTITION BY SomeNum ORDER BY UniqueNum ASC)+64) AS LetterSuffix
FROM #temp_Alpha_num
ORDER BY uniquenum,Somenum ASC;
so that when you get 121A to 121F and then maybe 101A to 101X or similar?
November 25, 2013 at 5:50 pm
What happens when you get to 4 letter combinations?
Could get nasty! 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 26, 2013 at 8:26 am
once the somenum exceeds the alphabet range it should be aa ab ac ad .but this code gives me other characters ([,],$,^,(,),)..
November 26, 2013 at 9:02 am
gavva.sravan (11/26/2013)
once the somenum exceeds the alphabet range it should be aa ab ac ad .but this code gives me other characters ([,],$,^,(,),)..
That is because this is a bad idea. Once you start "auto incrementing" characters you open up a whole possibility of bad things. What happens when you start getting into 3 and 4 letter words?
You already have a unique value per row. Why can't you use that? Or if you really need to use somenum why not cast it to a varchar add an underscore and then use row_number?
Something like this maybe?
select uniquenum, Somenum, CAST(Somenum varchar(10)) + '_' + cast(Suffix as varchar(5)) as SomeNum_PlusRowNum
from
(
SELECT UniqueNum
,Somenum
, ROW_NUMBER() OVER (PARTITION BY SomeNum ORDER BY UniqueNum ASC) AS Suffix
FROM #temp_Alpha_num
)
ORDER BY uniquenum, Somenum ASC;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply