Append Query + Concatenate - 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 Query + Concatenate

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 16:03:07

I have two tables, one with header records and one with "child" records. For every Header, there is one or more Children records. The header records are orders and the child records are the items on that order. So, for example an order can have more than one item.

I am essentially trying to denormalize these tables onto a "Shipping" table and I want to concatenate the items into one field for any orders that have more than one order.

For example. Order 1 has only 1 Item, so I want this:

ORDER1, ITEM1

Order 2 has 2 items, so I want it like this:

ORDER2, ITEM1 ~ ITEM2

Order 3 has 3 items, so I want it like this:

ORDER3, ITEM1 ~ ITEM2 ~ ITEM3

Does anyone have any suggestions on how to do this?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 16:07:10

What's the largest number of order items for a single order?

There are 10 types of people in the world, those that understand binary, and those that don't.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 16:20:05

Potentially infinite, but in practice we don't see more than a few items. Better question is probably how long do we want the resulting text string. If that is the question, I would say we want to limit the text string to 100 characters.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 17:21:18

Try this. It's a function combined with a simple select statement. There may be a way to do away with the function altogether, moving its functionality into the select, but I'm not sure how.


CREATE TABLE Orders (
	OrderID INT,
	OrderDesc VARCHAR(50)
)

CREATE TABLE OrderItems (
	OrderItemID INT,
	OrderID INT,
	ItemDesc VARCHAR(50)
)

INSERT INTO Orders
SELECT 1, 'Order 1'
UNION ALL SELECT 2, 'Order 2'
UNION ALL SELECT 3, 'Order 3'

INSERT INTO OrderItems
SELECT 1, 1, 'Item 1'
UNION ALL SELECT 2, 1, 'Item 2'
UNION ALL SELECT 3, 1, 'Item 3'
UNION ALL SELECT 4, 2, 'Item 1'
UNION ALL SELECT 5, 2, 'Item 2'
UNION ALL SELECT 6, 3, 'Item 1'
UNION ALL SELECT 7, 3, 'Item 2'
GO

CREATE FUNCTION dbo.CatOrders(@OrderID INT)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Items VARCHAR(100)
	SELECT @Items = ItemDesc + ISNULL(' ~ ' + @Items, '') 
	FROM OrderItems 
	WHERE OrderID = @OrderID
	ORDER BY OrderItemID DESC
	RETURN @Items 
END
GO

SELECT OrderDesc, dbo.CatOrders(OrderID)
FROM Orders o 

DROP TABLE Orders 
DROP TABLE OrderItems 
DROP FUNCTION dbo.CatOrders

There are 10 types of people in the world, those that understand binary, and those that don't.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 18:01:48

I think you're on the right track here. This is looking good. I'm not smart enough about TSQL to know how to fit in the pieces of my puzzle here. I think the inputs are going to be as follows:

Tables: Orders_Header and Orders_Child

Both tables are joined on the field Order_Number (varchar(50)). Orders_Child then has field Product_ID which is also varchar(50)

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 18:11:32

I did the following:

CREATE FUNCTION dbo.CatOrders(@Order_Number varchar(50))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Items VARCHAR(100)
SELECT @Items = Product_ID + ISNULL('~' + @Items, '')
FROM OS_Orders_Child
WHERE Order_Number = @Order_Number
ORDER BY Product_ID DESC
RETURN @Items
END
GO

And then separately ran:

SELECT Order_Number, dbo.CatOrders(Order_Number)
FROM OS_Orders_Header o

This is giving me EXACTLY what I needed. Thank you for your help. I think I can take it from here (though now I have to worry about what to do with the weights. I think I'll start a new post on that one sometime next week.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 18:12:01

Perhaps this:


CREATE FUNCTION dbo.CatOrders(@Order_Number INT)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Items VARCHAR(100)
	SELECT @Items = Product_ID + ISNULL(' ~ ' + @Items, '') 
	FROM Orders_Child
	WHERE Order_Number = @Order_Number
	ORDER BY OrderItemID DESC
	RETURN @Items 
END
GO

SELECT Order_Number, dbo.CatOrders(Order_Number)
FROM  Orders_Header o

The only bit I couldn't account for is the OrderItemID in the ORDER BY clause of the function. I used this to control the order in which the items appeared in the string. If you don't care about the item order, then you can omit it. Otherwise the function will need to be a little more complex. Unless of course there's a similar field in your Orders_Child table, in which case you could just use that.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 18:15:31

quote:
Originally posted by benking9987
This is giving me EXACTLY what I needed. Thank you for your help. I think I can take it from here (though now I have to worry about what to do with the weights. I think I'll start a new post on that one sometime next week.

Excellent. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 18:29:52

Oh, actually I forgot one crucial piece of information. As a piece of background, this text string ends up printing on a shipping label to indicate to a warehouse employee what to pick off the shelf. Given that, I need to put QTYs in the test string as well. Any idea how to incorporate that?

QTY is on the Order_Child table under the field "Qty"

So, ultimately I want it to look like this:

Order 1 has Item1 x 2 pcs, Item2 x 3 pcs, and Item3 x 1 pc

---> [2]Item1~[3]Item2~[1]Item3

Thoughts?

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 18:35:35

Oh, and QTY is datatype numeric(4,0)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 18:38:22

Using your version of the function:

CREATE FUNCTION dbo.CatOrders(@Order_Number varchar(50))
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Items VARCHAR(100)
	SELECT @Items = '[' + CAST(Qty AS VARCHAR) + ']' + Product_ID + ISNULL('~' + @Items, '')
	FROM OS_Orders_Child
	WHERE Order_Number = @Order_Number
	ORDER BY Product_ID DESC
	RETURN @Items
END
GO

There are 10 types of people in the world, those that understand binary, and those that don't.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 18:49:02

Nice. This is exactly what I got. Thank you. Want to try to help me handle the weights right here, or shall we try that on a separate post?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 18:52:42

Might as well tackle it here.

There are 10 types of people in the world, those that understand binary, and those that don't.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-26 : 18:59:18

And here is how you can do it without a function, using XML instead.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 19:11:02

Thanks Peso. I have ZERO knowledge of XML, so this will be a little bit difficult. Can you tell me what benefits there are of doing it with XML vs a function? The function takes about a millisecond to process, so I'm not seeing too many downsides.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-26 : 19:15:13

I think our biggest hurdle here is going to be getting the actual weight information regarding the items. I'm not familiar enough with SQL to know if I can do this, but here is what I have. The weight data is actually in a separate database called InventoryDB. The current DB we're working on is called OrdersDB.

With that said, I'm not sure if you can query data across databases within the same SQL instance, same server. I am reasonably familiar with SSIS that I might be able to figure out some data migration if needed, but I'd like to stay away from that where possible.

Can you let me know your thoughts on this?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 19:31:08

Here's how to do it with the XML method (which I'd forgotten about). However, this will change the order of the child records. That might not be an issue for you though.


SELECT DISTINCT	Order_Number,
		STUFF(( SELECT DISTINCT TOP 100 PERCENT '~[' + CAST(i.Qty AS VARCHAR) + ']' + i.Product_ID 
				FROM OS_Orders_Child AS i 
				WHERE i.Order_Number = o.Order_Number 
				ORDER BY '~[' + CAST(i.Qty AS VARCHAR) + ']' + i.Product_ID 
				FOR XML PATH('')), 1, 1, '') AS Items
FROM		OS_Orders_Header o

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 19:34:15

quote:
Originally posted by benking9987

I think our biggest hurdle here is going to be getting the actual weight information regarding the items. I'm not familiar enough with SQL to know if I can do this, but here is what I have. The weight data is actually in a separate database called InventoryDB. The current DB we're working on is called OrdersDB.

With that said, I'm not sure if you can query data across databases within the same SQL instance, same server. I am reasonably familiar with SSIS that I might be able to figure out some data migration if needed, but I'd like to stay away from that where possible.

Can you let me know your thoughts on this?

That's pretty easy. You can use fully qualified names to specify the database. eg:


SELECT blah 
FROM InventoryDB.dbo.tablename

Where InventoryDB is the database name, dbo is the schema, and tablename is obviously the table name.

There are 10 types of people in the world, those that understand binary, and those that don't.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-29 : 11:00:18

Excellent. Thanks DBA. I wasn't aware I could fully specify the database in the SQL language. Also, on the above, can you tell me if there is any benefit to doing the procedure in XML vs T-SQL?

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-29 : 12:59:02

Okay, to make things a little bit easier, I will include a step in my scheduled job that will pull over the weights to the Orders_Child table.

Now I just need to figure out how to sum up the weights for items that have multiple weights. One little caveat: the weights aren't exactly straight forward. I have a ItemWeight column (numeric(6,2)) and of UofM or Unit Of Measure column (varchar(2)). So the data can look like this:

ItemWeight UofM
__________ ____

6.00 OZ
2.70 LB
1.00 OZ
1.00 LB
13.00 OZ
4.00 LB

Operationally, I think the best way to do this is to convert any items with 'OZ' UofM to pounds by the formula [UofM]/16.

So, I just need a function that will return two things from the Orders_Child table. I need the Order_Number as well as WeightCalc which is the sum of the weights of all items on the order in pounds. WeightCalc I think needs to be numeric(10,6) just to make sure we have space for all possible decimals we might encounter.

For example, if I have an order with two items as follows:

Order1, Item1, 6.00, OZ
Order1, Item2, 1.00, LB

The WeightCalc on this one would be 1.375 (1 + 6/16) = 1.375

If I have:

Order2, Item1, 2.50, LB

Then the WeightCalc would just be 2.50

If I have:

Order3, Item1, 1.00, LB
Order3, Item2, 2.00, LB

Then the WeigthCalc would be 3.00

If I have:

Order4, Item1, 1.00, OZ
Order4, Item2, 2.00, OZ
Order4, Item3, 3.00, OZ

Then WeightCalc would be .0375 (1/16 + 2/16 + 3/16)

Thank you in advance for all your help.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-03-29 : 13:25:55

I fiddled around with what we had on some of the other statements to create the function. Here is what I have:


CREATE FUNCTION dbo.OrdersWeight(@Order_Number varchar(50))
RETURNS NUMERIC(10,6)
AS
BEGIN
	DECLARE @ItemWeight NUMERIC(10,6)
	SELECT @ItemWeight = CASE when [UofM] = 'OZ' then ([ItemWeight]*[QTY])/16 else ([ItemWeight]*[QTY]) end
	FROM OS_Orders_Child
	WHERE Order_Number = @Order_Number
	RETURN @ItemWeight 
END
GO

This is currently doing the calculation correctly of converting an 'OZ' item to 'LB' and and multiplying by the Order QTY, however it is not adding up the values over all items on the order. Rather, it is just returning the calculation for the one item that happens to weight the most on the order.

Any thoughts?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources