Append character in SELECT - 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)

Append character in SELECT

Author

Topic

trevorhartmanhadw
Starting Member

4 Posts

Posted - 2007-09-26 : 20:15:30

Hey,

I have a SELECT on an PurchaseOrders table, joined to LineItems, joined to OrderAddresses. Each LineItem can be shipped to a unique address for the order.

Here is some sample data


Date                    OrderNumber                             OrderAddressId
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{8f876f41-5f15-4682-afa6-4a14b0372f6d}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{ea175b8d-627a-4fea-b841-5a2bb6bae608}

So this is the data for one order - 4 items, the top 2 being shipped to an address, the 3rd line to another address, the 4th line to yet another unique address. What I would like to do is for each unique address, append a character (A, b, c ...) to the end of the OrderNumber so as to make it unique to each shipment. After modifying the data it should look like this:


Date                    OrderNumber                             OrderAddressId
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19	{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19-A	{8f876f41-5f15-4682-afa6-4a14b0372f6d}
9/12/2007 10:41:32 PM	1ff118ae-3a22-4912-96cd-b75da7d6ca19-B	{ea175b8d-627a-4fea-b841-5a2bb6bae608}

How would one go about that?

Thanks,
Trevor

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-26 : 20:52:04

[code]DECLARE @order TABLE
(
[Date] datetime,
OrderNumber varchar(50),
OrderAddressId varchar(50)
)

INSERT INTO @order
SELECT '9/12/2007 10:41:32 PM', '1ff118ae-3a22-4912-96cd-b75da7d6ca19', '{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}' UNION ALL
SELECT '9/12/2007 10:41:32 PM', '1ff118ae-3a22-4912-96cd-b75da7d6ca19', '{4e48d83d-ee95-46bc-81a6-8ffeac66fdea}' UNION ALL
SELECT '9/12/2007 10:41:32 PM', '1ff118ae-3a22-4912-96cd-b75da7d6ca19', '{8f876f41-5f15-4682-afa6-4a14b0372f6d}' UNION ALL
SELECT '9/12/2007 10:41:32 PM', '1ff118ae-3a22-4912-96cd-b75da7d6ca19', '{ea175b8d-627a-4fea-b841-5a2bb6bae608}'

SELECT Date,
NewOrderNumber = OrderNumber +
CASE WHEN rank = 0 THEN ''
ELSE '-' + CHAR(ASCII('@') + rank)
END,
OrderAddressId
FROM
(
SELECT *,
rank = dense_rank() OVER (PARTITION BY OrderNumber ORDER BY OrderAddressId) - 1
FROM @order
) o
/*
Date NewOrderNumber OrderAddressId
------------------------ --------------------------------------- --------------------------------------
2007-09-12 22:41:32.000 1ff118ae-3a22-4912-96cd-b75da7d6ca19 {4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
2007-09-12 22:41:32.000 1ff118ae-3a22-4912-96cd-b75da7d6ca19 {4e48d83d-ee95-46bc-81a6-8ffeac66fdea}
2007-09-12 22:41:32.000 1ff118ae-3a22-4912-96cd-b75da7d6ca19-A {8f876f41-5f15-4682-afa6-4a14b0372f6d}
2007-09-12 22:41:32.000 1ff118ae-3a22-4912-96cd-b75da7d6ca19-B {ea175b8d-627a-4fea-b841-5a2bb6bae608}

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

trevorhartmanhadw
Starting Member

4 Posts

Posted - 2007-09-27 : 12:57:48

wow, nice work! thanks for your help

trevorhartmanhadw
Starting Member

4 Posts

Posted - 2007-09-27 : 13:25:32

actually, i'm getting an error:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'SELECT'.
Msg 195, Level 15, State 10, Line 20
'dense_rank' is not a recognized function name.

dense_rank function doesn't exist? I'm in SQL Server Management Studuio on a SQL SERVER 2005 DB.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:55:01

khtan's example works fine here, I just cut&pasted it and pressed "Execute"

Kristen

trevorhartmanhadw
Starting Member

4 Posts

Posted - 2007-09-27 : 14:08:55

I did the same, in SQL SErver Management Studio...Am I doing something wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 14:12:22

Please check your compatibility level!
It should be set to 90.