Numbering Results

[sharmstr]

I have two tables. One with orders and one with lines items for the orders. The line items have a unique code that relates to the line item number. When sorting on this unique code, you are able to see the order in which the item was added to the order. I'd like a query to return an actual line number instead of this unique code. Here's the layout

Order Table (orders):
orderNumber

Line Item Table (orderLines):
orderNumber
lineItem
partNumber

Example Query:
select orderNumber, lineItem, partNumber
FROM orders INNER JOIN
orderLines ON orders.csono = orderLines.csono

Example Data:
orderNumber, lineItem, partNumber
12345, _1LK0LSRTJ, part123
12345, _1LK0LSXU7, part456
12345, _1LK0LT39X, part789

I would like the results to look like this
12345-1, part123
12345-2, part456
12345-3, part789

Thank you for any help.

[BitBlt]

I assumed that you wanted each new order number to start with line number 1 like so:
12345-1
12345-2
12345-3
23456-1
23456-2
etc.

After playing a little fast and loose with your sample query and test data, here's what I came up with:

select 
cast(ord.orderNumber as varchar(5)) + '-' + cast(ROW_NUMBER() over (PARTITION BY ord.orderNumber order by partnumber asc) as varchar(10)) as OrderLineNumber
, partNumber
FROM dbo.orders ord
INNER JOIN dbo.orderLines ordln
ON ord.orderNumber = ordln.orderNumber

The important part here is the whole PARTITION BY clause. You might want to look in the help file if you desire more details.

Here is the scenario I built for testing on SQL2008:

create table dbo.orders
(orderNumber int)

create table dbo.orderLines
(
orderNumber int,
lineItem varchar(10),
partNumber varchar(10)
)

insert into dbo.orders
(orderNumber) values
(12345), (23456), (34567), (45678)

insert into dbo.orderLines
(orderNumber, lineItem, partNumber)
values
(12345, 'A1LK0LSRTJ', 'parta123'),
(12345, 'A1LK0LSXU7', 'parta456'),
(12345, 'A1LK0LT39X', 'parta789'),
(23456, 'B1LK0LSRTJ', 'partb123'),
(23456, 'B1LK0LSXU7', 'partb456'),
(23456, 'B1LK0LT39X', 'partb789'),
(34567, 'C1LK0LSRTJ', 'partc123'),
(34567, 'C1LK0LSXU7', 'partc456'),
(34567, 'C1LK0LT39X', 'partc789'),
(45678, 'D1LK0LSRTJ', 'partd123'),
(45678, 'D1LK0LSXU7', 'partd456'),
(45678, 'D1LK0LT39X', 'partd789')

And, finally, here are my results:

OrderLineNumber	partNumber
12345-1	         parta123
12345-2	         parta456
12345-3	         parta789
23456-1	         partb123
23456-2	         partb456
23456-3	         partb789
34567-1	         partc123
34567-2	         partc456
34567-3	         partc789
45678-1	         partd123
45678-2	         partd456
45678-3	         partd789

Hope this helps! Good luck!

[sharmstr]

This looks great, though doesnt work with SQL2000. I know, I know, I should have mentioned that. I'm really sorry for wasting you time and do appreciate the response. I'm looking at using IDENTITY instead of ROW_NUMBER now.

[BitBlt]

Aw, dang! Oh, well...it was fun to try.

I do have a cursor-based solution for SQL2000, though. It's not pretty, but it works.

create table #temp
(orderNumber int,
orderLineItemNumber int,
lineItem varchar(10),
partNumber varchar(10)
)

declare @ordNbr int
declare @ordFetchStatus int
declare @ordLnNbr int
declare @ordLnFetchStatus int
declare @lineItem varchar(10)
declare @partNumber varchar(10)

declare ordCursor cursor for select ordernumber from dbo.orders
open ordCursor
fetch next from ordCursor into @ordNbr
select @ordFetchStatus = @@fetch_status
while @ordFetchStatus = 0
begin
    set @ordLnNbr = 0
    declare ordLnCursor cursor for select lineItem, partNumber from dbo.orderLines where ordernumber = @ordNbr order by lineitem
    open ordLnCursor
    fetch next from ordLnCursor into @lineItem, @partNumber
    select @ordLnFetchStatus = @@fetch_status
    while @ordLnFetchStatus = 0
    begin
        set @ordLnNbr = @ordLnNbr + 1
        insert into #temp (orderNumber, orderLineItemNumber, lineItem, partNumber)
        values (@ordNbr, @ordLnNbr, @lineItem, @partNumber)
        fetch next from ordLnCursor into @lineItem, @partNumber
        select @ordLnFetchStatus = @@fetch_status
    end
    close ordLnCursor
    deallocate ordLnCursor
    fetch next from ordCursor into @ordNbr
    select @ordFetchStatus = @@fetch_status
end
close ordCursor
deallocate ordCursor

select cast(orderNumber as varchar(5)) + '-' + cast(orderLineItemNumber as varchar(5)) as OrderLineNumber, partNumber from #temp 
drop table #temp

Tested with the same table structure and test data, seems to work fine.

[sharmstr]

WOW. That is ugly. But it works! No way I could have figured that out. Thanks a million!

[BitBlt]

You're welcome, happy to help. Be sure to mark the thread "Solved".

[sharmstr]

I have revisted this and want to add a bit more information. As previously stated, the data lives on SQL2000, but on my client machine, I've installed SQL2012. On the SQL2012 box, I've created a link to the SQL2000 thus allowing me to run newer commands. I've set up a view on the SQL2012 box that utilizes the bitblt original solution using ROWNUMBER. Even though its acessing data via a linked server, its fast and a lot less clunky than the SQL2000 solution. Okay, now on to my issue.

Each line in the Line Item Table (orderLines) has a qty ordered. I'd like to add x amount of rows depending on how many items where ordered and add a count to the end of the order number. Here's the data

Order Table (orders):
orderNumber

Line Item Table (orderLines):
orderNumber
lineItem
partNumber
orderQty

Example Query:
select orderNumber, lineItem, partNumber, orderQty
FROM orders INNER JOIN
orderLines ON orders.csono = orderLines.csono

Example Data:
orderNumber, lineItem, partNumber, orderQty
12345, _1LK0LSRTJ, part123,1
12345, _1LK0LSXU7, part456,2
12345, _1LK0LT39X, part789,5

I would like the results to look like this
12345-1-1, part123
12345-2-1, part456
12345-2-2, part456
12345-3-1, part789
12345-3-2, part789
12345-3-3, part789
12345-3-4, part789
12345-3-5, part789

Again, I'm using the below query in my view. Any help modifying it to add the new data is greatly appreciated.

select
cast(ord.orderNumber as varchar(5)) + '-' + cast(ROW_NUMBER() over (PARTITION BY ord.orderNumber order by partnumber asc) as varchar(10)) as OrderLineNumber
, partNumber
FROM dbo.orders ord
INNER JOIN dbo.orderLines ordln
ON ord.orderNumber = ordln.orderNumber

Thank you
shawn

[sharmstr]

I was able to sort this out with a temp table and cursors.