Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Counter inside SELECT Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, 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.
Post #1428011
Posted Thursday, March 7, 2013 8:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 8:28 AM
Points: 955, Visits: 2,638
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
Post #1428015
Posted Thursday, March 7, 2013 8:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, 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.
Post #1428022
Posted Thursday, March 7, 2013 8:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1428035
Posted Thursday, March 7, 2013 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1428043
Posted Thursday, March 7, 2013 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, 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.
Post #1428055
Posted Thursday, March 7, 2013 9:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 8:28 AM
Points: 955, Visits: 2,638
Thanks for the feedback, I was glad to help.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1428057
Posted Thursday, March 7, 2013 2:38 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



Post #1428272
Posted Thursday, March 7, 2013 8:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, 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.
Post #1428351
Posted Friday, March 8, 2013 8:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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!


 
Post #1428628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse