Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Merging two rows into a single one

    Good evening. I've just recently started to understand the SQL language. I've purchased my first book. I'm an internet retailer but I'm using a shipping program called endicia professional that allows for database manipulation to make my processing easier. I've managed to fix the database here and there but have had an issue combining orders from a single customer when theybuy more than one item. Ideally I would like to have it combine rows when a customer purchases items going to the same address. To avoid having an issue where the address line is the same ie two people live in the same appt complex and it combines these I thought we could use qualifiers as the purchase will have name, order Id that should be unique enough

    Order-id name address sku
    1234 John 46 easy ln. A27
    1234 John 46 easy ln. B32

    Results:
    Order-id name address sku
    1234 John 46 easy ln. A27,b32

    I apologize as my understanding and knowledge is very new of SQL but I would like to thank you for your assistance ahead of time.

  2. #2
    Join Date
    Sep 2010
    Posts
    153
    Select a.order-id, a.name, a.address, a.sku + b.sku from a.tablename, b.tablename where a.order-id = b.order-id and a.name = b.name and a.address = b.address and a.sku != b.sku



    I don't know whether it will work or not since I am in office and don't have sql server. I feel it will work. If not, i ll get it done later...

    I would suggest you to go through normalization thoroughly. Structure of your table is not good.

  3. #3
    Join Date
    Sep 2010
    Posts
    153
    a.sku + ',' + b.sku

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I've just recently started to understand the SQL language. I've purchased my first book. I'm an internet retailer but I'm using a shipping program called endicia professional that allows for database manipulation to make my processing easier.
    I guess Endicia Professional is your core business application and you, as a database novice, are changing the underlying database of it? Or are you just writing queries on top of it?

    Anyway take frequent backups, and perform tests so that you can verify that you can actually restore them.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Using this program I upload a Csv which serves as my database for which I wish to manipulate. All orders for my compan are processed from this Csv daily and each day the items are rather unique in comparison to the previous or following day. I am writing quiries on top of endicia professional. On any given day we may have 1500 orders. About 1-2% are comprised of information I wish to combine. The items written would need to be able to convert any and all lines that are the same except in sku and in those cases merge and have an additive effect on sku. This would allow me to process their order without shipping them two packages as each line in my Csv represents one package. Unfortunately when a single customer orders multiple items currently they receive that same number of packages. I will try to give a better example of the information provided and the result needed once I get to the office shortly. Also I do have the current script saved as a back up and have had some experience adding and subtracting things but I do appreciate the warning .

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OK, now it makes sense.

    Try this:
    Code:
    WITH CTE AS
    (Select order_id, name, address,
    from tablename
    GROUP BY order_id
    HAVING COUNT(*) > 1
    )
    SELECT t1.order_id, t1.name, t1.address,
    	(SELECT sku + ', '
    	FROM tablename as t2
    	WHERE t1.order_id = t2.order_id
    	ORDER BY sku
    	FOR XML PATH('')
    	) AS sku
    FROM CTE as t1
    GROUP BY t1.order_id, t1.name, t1.address;
    It will give all sku's for orders with more than one product.

    I don't see the problem. Many Orders have multiple products. You are denormalising a normalised system.
    On an order sheet, you can see multiple lines, one line per product, with its sku, a short description, number ordered, number shipped, unit price, reduction, total price, ... You are loosing all of that.
    Last edited by Wim; 03-05-12 at 19:22.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Mar 2012
    Posts
    3
    Hello there again. Thank you for your assistance. I have attached a CSV to assist in what im trying to yield. Im not trying to lose any of the items you have mentioned would be lost and that would be the case if i implemented the way you have advised. I apologize for my lack of clarity. To further assist I have attached an tab delimited sheet showing the type of information layout that is generally converted and the database it will be pulling from. Please let me know how i might assist further.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •