Autogenerate values based on condition:

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Autogenerate values based on condition:

Author

Topic

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-02 : 20:33:05

Hey guys,

Not sure if this question has been answered before, but I've been looking around for a bit now and have not yet come up with an answer.

I found a few articles on autogeneration, but it did not take care of change in conditions like my case below.

If there already is a reply to this one, can you please direct me to the link? Thank you.

I have table [TableA] with the following structure / data:
ColumnA|ColumnB|ColumnC
01|AAA|NULL
01|BBB|NULL
01|CCC|NULL
02|DDD|NULL
02|EEE|NULL
02|FFF|NULL

All three columns have datatypes = varchar

ColumnC needs to store a formatted autogenerated number upto 5 characters padded with "0"(zero). This autogeneration is dependant on ColumnA and will happen in increments of 10. For every change in ColumnA, ColumnC value will be reset to 10 and start incrementing once again.

eg.
ColumnA|ColumnB|ColumnC
01|AAA|00010
01|BBB|00020
01|CCC|00030
02|DDD|00010
02|EEE|00020
02|FFF|00030

Thanks in advance for any help.

mattyblah
Starting Member

49 Posts

Posted - 2007-08-02 : 22:23:26

a query to get what you want could be....


select columna, columnb,
 right('00000' + cast(10 * row_number() over (partition by columna order by columnb) as varchar(5)), 5) as columnc
 from tablea
 order by columna, columnb

does that help?

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-02 : 22:30:45

DECLARE @TableA TABLE (ColumnA varchar(2), ColumnB varchar(3))

INSERT INTO @TableA SELECT '01', 'AAA'
UNION SELECT '01', 'BBB'
UNION SELECT '01', 'CCC'
UNION SELECT '02', 'DDD'
UNION SELECT '02', 'EEE'
UNION SELECT '02', 'FFF'

SELECT A.ColumnA, A.ColumnB,
RIGHT(CONVERT(varchar, (SELECT COUNT(*) FROM @TableA Z WHERE Z.ColumnA = A.ColumnA AND Z.ColumnB <= A.ColumnB) * 10 + 100000), 5)
FROM @TableA A
ORDER BY A.ColumnA, A.ColumnB

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-02 : 22:43:28

quote:
Originally posted by mattyblah

a query to get what you want could be....

select columna, columnb,
right('00000' + cast(10 * row_number() over (partition by columna order by columnb) as varchar(5)), 5) as columnc
from tablea

does that help?

Hi Matt,

Sorry I should have mentioned I'm working on SQL 2000 not 2005. I think the OVER clause and row_number() function are for 2005?

The query you sent me did not work. Any other clues? Thanks!

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-02 : 22:51:09

quote:
Originally posted by Koji Matsumura

DECLARE @TableA TABLE (ColumnA varchar(2), ColumnB varchar(3))

INSERT INTO @TableA SELECT '01', 'AAA'
UNION SELECT '01', 'BBB'
UNION SELECT '01', 'CCC'
UNION SELECT '02', 'DDD'
UNION SELECT '02', 'EEE'
UNION SELECT '02', 'FFF'

SELECT A.ColumnA, A.ColumnB,
RIGHT(CONVERT(varchar, (SELECT COUNT(*) FROM @TableA Z WHERE Z.ColumnA = A.ColumnA AND Z.ColumnB <= A.ColumnB) * 10 + 100000), 5)
FROM @TableA A
ORDER BY A.ColumnA, A.ColumnB

Brilliant Koji, Thanks! This seems to be working. However its already 8:00 pm at the moment, the real test will come tomorrow morning with some live production data.

Can you please check in tomorrow same time as your post today and I will let you know if it all went through fine.

Thanks all for your immediate replies.

Feel free to post in other methods of doing this if you think of any, will definitely improve my learning curve. Gnight!

mattyblah
Starting Member

49 Posts

Posted - 2007-08-03 : 12:34:26

quote:
Originally posted by Esc2Xtc

quote:
Originally posted by mattyblah

a query to get what you want could be....

select columna, columnb,
right('00000' + cast(10 * row_number() over (partition by columna order by columnb) as varchar(5)), 5) as columnc
from tablea

does that help?

Sorry bout that. Just figured that since you're posting on the 2005 forum you were on 2005. There is also a forum for T-SQL 2000.
Hi Matt,

Sorry I should have mentioned I'm working on SQL 2000 not 2005. I think the OVER clause and row_number() function are for 2005?

The query you sent me did not work. Any other clues? Thanks!

Oh, probably should have posted on the Transact-SQL (2000) forum instead ;)

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-03 : 14:12:59

ouch - so i did without realizing, my appologies, thanks once again.

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-06 : 20:00:15

quote:
Originally posted by Koji Matsumura

DECLARE @TableA TABLE (ColumnA varchar(2), ColumnB varchar(3))

INSERT INTO @TableA SELECT '01', 'AAA'
UNION SELECT '01', 'BBB'
UNION SELECT '01', 'CCC'
UNION SELECT '02', 'DDD'
UNION SELECT '02', 'EEE'
UNION SELECT '02', 'FFF'

SELECT A.ColumnA, A.ColumnB,
RIGHT(CONVERT(varchar, (SELECT COUNT(*) FROM @TableA Z WHERE Z.ColumnA = A.ColumnA AND Z.ColumnB <= A.ColumnB) * 10 + 100000), 5)
FROM @TableA A
ORDER BY A.ColumnA, A.ColumnB

Hi Koji,

Sorry for replying late but I didn't get into work the last few days, it being a weekend. Not sure if you will read this, but since I said I would write in, here's my reply.

There were one case where the query might fail and that was when Column B values were not to be ordered, in which case the wrong records would get the autogenerated numbers (as below). If I pulled out the order by clause, the wrong records were once again being autonumbered.

ColA, ColB, ColC (Autonumber after Order by)
'01', 'XXX', 2 (should be 1)
'01', 'BBB', 1 (should be 2)
'02', 'YYY', 4 (should be 3)
'02', 'EEE', 3 (should be 4)

I took care of this case by inserting a temporary identity column, sorting on that and then autonumbering. Once I got the autonumbers, I just deleted the identity column.

The query worked fine. Thanks a million and let me know if I can be of any assistance. Have a great day!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-06 : 20:10:47

I have moved this thread to the Transact-SQL 2000 forum.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-06 : 20:46:52

quote:
Originally posted by tkizer

I have moved this thread to the Transact-SQL 2000 forum.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Thanks Tara, sorry for the inconvenience

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources