Counter inside SELECT

Question

Post reply

Counter inside SELECT

RZ52

Mr or Mrs. 500

Points: 536
More actions
March 7, 2013 at 8:10 am

#274889

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_NAMEC_CODE
----------------------
John CL1
Jake CL2
Joe CL3
Jane CL4
Ray CL5
James CL5
Fred CL5
Mac CL5
But I need it to be :
C_NAMEC_CODE
----------------------
John CL1
Jake CL2
Joe CL3
Jane CL4
Ray CL5
James CL6
Fred CL7
Mac CL8
Thanks in advance for helps.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

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 Mr or Mrs. 500 Points: 536 More actions · Answer 2

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 SSC Guru Points: 59322 More actions · Answer 3

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[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

RZ52 (3/7/2013)

...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

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 5

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 SSC-Insane Points: 21601 More actions · Answer 6

Thanks for the feedback, I was glad to help.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 7

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 Mr or Mrs. 500 Points: 536 More actions · Answer 8

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 SSCrazy Eights Points: 9893 More actions · Answer 9

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!