SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counter inside SELECT


Counter inside SELECT

Author
Message
RZ52
RZ52
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 74
Hi,

I have the following scenario :


DECLARE @TEST1 TABLE (C_NAME varchar(10), C_CODE VARCHAR(5))
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('John', 'CL1')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jake', 'CL2')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Joe', 'CL3')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jane', 'CL4')


DECLARE @TEST2 TABLE (P_NAME varchar(10), P_CODE VARCHAR(5))
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Mac', 'PL94')


DECLARE @x INT
SELECT @x = MAX(CONVERT(INT,SUBSTRING(C_CODE,3,2))) FROM @TEST1

INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+1)
FROM @TEST2)



I need the new rows add to @Table1 but the C_CODE adds incrementally.

What I have in @Table1 after running the above code is :


C_NAME C_CODE
----------------------
John CL1
Jake CL2
Joe CL3
Jane CL4
Ray CL5
James CL5
Fred CL5
Mac CL5


But I need it to be :


C_NAME C_CODE
----------------------
John CL1
Jake CL2
Joe CL3
Jane CL4
Ray CL5
James CL6
Fred CL7
Mac CL8


Thanks in advance for helps.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 3232
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
RZ52
RZ52
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 74
Jason-299789 (3/7/2013)
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.


Dear Jason,

Thanks for guidance. However, in my real table the number coming with CL doesn't necessarily represent row number as some of rows are removed form table.
What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5094 Visits: 5478
As you provided very limited details of your requirements I can suggest you reading this:
http://www.sqlservercentral.com/articles/T-SQL/68467/

This method may work for you.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16602 Visits: 19557
RZ52 (3/7/2013)
[quote]...What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.


That's exactly what Jason's code is designed to do. Did you run it and look at the results? In what way does it not fit your specification?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
RZ52
RZ52
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 74
RZ52 (3/7/2013)
Jason-299789 (3/7/2013)
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.


Dear Jason,

Thanks for guidance. However, in my real table the number coming with CL doesn't necessarily represent row number as some of rows are removed form table.
What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.


Dear Jason,

Your solution worked perfectly. That was my mistake to handle the OVER (ORDER BY) in wrong way.

Now it works.


Best Regards.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 3232
Thanks for the feedback, I was glad to help.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 1721
Just to add another option with a slightly more complex query that doesn't require a seed value. Also, by adding identity columns it will keep the rows in the same order.



IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
DROP TABLE #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2

CREATE TABLE #Test1 (ID INT IDENTITY(1,1),C_NAME VARCHAR(20),C_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('John','CL1')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jake','CL2')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Joe','CL3')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jane','CL4')

CREATE TABLE #Test2 (ID INT IDENTITY(1,1),P_NAME VARCHAR(20),P_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mac', 'PL94')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Martin', 'X911')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('George', 'X922')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Jon', 'ABCDE99993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mary', 'BBB4')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Carl', 'DF691')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mackenzie', 'FGL9')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Sally', 'QW9883')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Karen', 'OPP')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Marty', 'ZZ11')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Abram', 'X122')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Zonnie', 'QWERTY9993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ryan', 'DCDCDC4')






;WITH cte AS
(
SELECT
R1.[C_NAME]
,'CL'+CAST(ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY R1.ID)+[MAX_C_CODE] AS VARCHAR(20)) AS [NEW_C_CODE]
FROM
(
SELECT DISTINCT
P1.ID
,P1.[P_NAME] AS [C_NAME]
,STUFF(MAX(C1.[C_CODE]) OVER (PARTITION BY 1),1,2,'') AS [MAX_C_CODE]
FROM
#Test1 C1
CROSS APPLY
#Test2 P1
WHERE
P1.ID > C1.ID
) R1
)
INSERT INTO #Test1
SELECT
C_NAME
,NEW_C_CODE
FROM
cte


SELECT * FROM #Test1



RZ52
RZ52
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 74
Steven Willis (3/7/2013)
Just to add another option with a slightly more complex query that doesn't require a seed value. Also, by adding identity columns it will keep the rows in the same order.



IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
DROP TABLE #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2

CREATE TABLE #Test1 (ID INT IDENTITY(1,1),C_NAME VARCHAR(20),C_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('John','CL1')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jake','CL2')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Joe','CL3')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jane','CL4')

CREATE TABLE #Test2 (ID INT IDENTITY(1,1),P_NAME VARCHAR(20),P_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mac', 'PL94')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Martin', 'X911')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('George', 'X922')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Jon', 'ABCDE99993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mary', 'BBB4')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Carl', 'DF691')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mackenzie', 'FGL9')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Sally', 'QW9883')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Karen', 'OPP')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Marty', 'ZZ11')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Abram', 'X122')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Zonnie', 'QWERTY9993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ryan', 'DCDCDC4')






;WITH cte AS
(
SELECT
R1.[C_NAME]
,'CL'+CAST(ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY R1.ID)+[MAX_C_CODE] AS VARCHAR(20)) AS [NEW_C_CODE]
FROM
(
SELECT DISTINCT
P1.ID
,P1.[P_NAME] AS [C_NAME]
,STUFF(MAX(C1.[C_CODE]) OVER (PARTITION BY 1),1,2,'') AS [MAX_C_CODE]
FROM
#Test1 C1
CROSS APPLY
#Test2 P1
WHERE
P1.ID > C1.ID
) R1
)
INSERT INTO #Test1
SELECT
C_NAME
,NEW_C_CODE
FROM
cte


SELECT * FROM #Test1







Dear Steven,

Thanks for suggestion. I think this would also help me in other situations as this has less limitation.

Regards.
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 1721
RZ52 (3/7/2013)
Thanks for suggestion. I think this would also help me in other situations as this has less limitation.

Another advantage of this method is that you can make an inline-table function (itvf) out of it if it's going to be something you need to run frequently.

Best of luck with your SQL projects!


 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search