October 27, 2014 at 6:38 am
Hi,
I am having a
source table:
#test1(studID, FIrstNAme,LasteName)
Target Table #Test2(StudID, UserName)
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar
2, Arun, Pawar
3, Ashis, Panday
Then i want to create username like:
apawar
apawar01
apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value aviliable if not then create with lower value and insert.
kindly suggest ideas
Thanks.
October 27, 2014 at 8:13 am
ROW_NUMBER() is your friend:
DECLARE @test1 TABLE (
studID int,
FIrstNAme varchar(50),
LastName varchar(50)
)
DECLARE @Test2 TABLE (
StudID int,
UserName varchar(50)
)
INSERT INTO @test1 VALUES
(1, 'Abhas', 'Pawar' ),
(2, 'Arun', 'Pawar' ),
(3, 'Ashis', 'Panday')
SELECT StudId,
UserName = UserName + CASE RN WHEN 0 THEN '' ELSE RIGHT('00' + CAST(RN AS varchar(2)),2) END
FROM (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY studId) - 1
FROM (
SELECT *, UserName = LEFT(FirstName,1) + RIGHT(LastName,LEN(LastName)-1)
FROM @test1
) AS SourceData
) AS RankedData
ORDER BY StudId
Hope this helps
-- Gianluca Sartori
October 27, 2014 at 8:33 am
Thanks Gianluca ,
But if username is already exists then failing.
For below data also giving same output:
DECLARE @test1 TABLE (
studID int,
FIrstNAme varchar(50),
LastName varchar(50)
)
DECLARE @Test2 TABLE (
StudID int,
UserName varchar(50)
)
INSERT INTO @test1 VALUES
(1, 'Abhas', 'Pawar' ),
(2, 'Arun', 'Pawar' ),
(3, 'Ashis', 'Panday')
INSERT INTO @test2 VALUES
(5, 'APawar01' )
SELECT StudId,
UserName = UserName + CASE RN WHEN 0 THEN '' ELSE RIGHT('00' + CAST(RN AS varchar(2)),2) END
FROM (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY studId) - 1
FROM (
SELECT *, UserName = LEFT(FirstName,1) + RIGHT(LastName,LEN(LastName))
FROM @test1
) AS SourceData
) AS RankedData
ORDER BY StudId
current output:
StudIdUserName
1APawar
2APawar01
3APanday
Expected output:
StudIdUserName
1APawar
5APawar01
2APawar02
3APanday
Thanks,
Abhas
October 27, 2014 at 9:10 am
So if there is a duplicate you want to create a new row? In your example for Pawar you have two rows of data but three in the output.
_______________________________________________________________
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/
October 27, 2014 at 9:16 am
Yes Sean,
Exactly......
for example if apawar02 and apawar05 exists in table2
and in table 1 there are apawar, apwar01, ...........apawar10 then while inserting into table2,
apawar will inserted as apawar
apawar01 will become apawar02
apawar02 will become apawar03
apawar03 will become apawar04
but
apawar04 will become apawar06 as apawar05 already exists.
Thanks,
October 27, 2014 at 9:29 am
Don't do this. Use a new column for the iteration of name, it will be much easier. How many loads do you perform into the target table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply