populating query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

populating query

Author

Topic

duby
Starting Member

3 Posts

Posted - 2008-12-15 : 08:11:22

I have table with 4 columns

No. DocumentNo. ArticleNo. LineNo.
1 PR-07-00001 ACR-0215 NULL
2 PR-07-00001 ACR-0262 NULL
3 PR-07-00001 ACR-0262 NULL
4 PR-07-00001 ACR-0262 NULL
5 PR-07-00001 ACR-0262 NULL
6 PR-07-00001 ACR-0299 NULL
7 PRI-05-112 ACR-0272 NULL
8 PRI-05-113 ACR-0277 NULL
9 PRI-05-113 ACR-0277 NULL
10 PRI-05-113 ACR-0277 NULL
11 PRI-05-113 ACR-0268 NULL
12 PRI-05-114 ACR-0248 NULL
13 PRI-05-114 ACR-0248 NULL
14 PRI-05-114 ACR-0298 NULL
15 PRI-05-118 ACR-0264 NULL

What I need to get is:
No. DocumentNo. ArticleNo. LineNo.
1 PR-07-00001 ACR-0215 10000
2 PR-07-00001 ACR-0262 10000
3 PR-07-00001 ACR-0262 20000
4 PR-07-00001 ACR-0262 30000
5 PR-07-00001 ACR-0262 40000
6 PR-07-00001 ACR-0299 10000
7 PRI-05-112 ACR-0272 10000
8 PRI-05-113 ACR-0277 10000
9 PRI-05-113 ACR-0277 20000
10 PRI-05-113 ACR-0277 30000
11 PRI-05-113 ACR-0268 10000
12 PRI-05-114 ACR-0248 10000
13 PRI-05-114 ACR-0248 20000
14 PRI-05-114 ACR-0298 10000
15 PRI-05-118 ACR-0264 10000

How to populate LineNo. column?

Thanks
Dubravko

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-15 : 08:22:59

Use the new ROW_NUMBER() function

SELECT *, 10000 * ROW_NUMBER() OVER (PARTITION BY DocumentNo, ArticleNo ORDER BY No) AS LineNo
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 09:03:51

[code]UPDATE t
SET t.LineNo=t.Seq
FROM
(
SELECT *,
10000 * ROW_NUMBER() OVER (PARTITION BY DocumentNo, ArticleNo ORDER BY No) AS Seq
FROM Table1
)t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources