Multiple Rows to Single Row

[PCHomepage]

Amazon.com, in their infinite wisdom, creates seller database dumps that have details for each transaction in separate rows and, while I know SQL well, I am a bit stumped on how to get started on a query to re-insert them in a usable way into another table in a single row. I noticed that the actual text changed somewhat over time but the basic structure remained the same with data like this:

Date Order ID SKU Transaction type Payment Type Payment Detail Amount Quantity Product Title

2/13/10 103-1058945-1140208 00-AAAA-BBBB Order Payment Product charges $29.99 1 Book Name

2/13/10 103-1058945-1140208 00-AAAA-BBBB Order Payment Amazon fees Commission $-4.50 Book Name

2/13/10 103-1058945-1140208 00-AAAA-BBBB Order Payment Amazon fees Fixed closing fee $-0.99 Book Name

2/13/10 103-1058945-1140208 00-AAAA-BBBB Order Payment Amazon fees Variable closing fee $-0.80 Book Name

2/13/10 103-1058945-1140208 00-AAAA-BBBB Order Payment Other Shipping $2.98 Book Name

and is grouped by the Order ID column which is what separates one order from another. I want to take this data, run a query on it, and have the end results be on one single line for each order and in specific fields in the target table. I won't be using every column of data here and others might be combined into single columns but that's another issue that I am sure I can do myself.

Can anyone suggest how to begin? Thanks in advance!

Don P

 

[KarveR]

If you have a linux box, I'd have a look at AWK to format this into a suitable insert statement, rather than trying to do anything in the database.





______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

SELECT Date
     , OrderID
     , SKU
     , MAX(CASE WHEN PaymentType = 'Product charges' 
                THEN Amount ELSE NULL END) AS ProductCharges
     , MAX(CASE WHEN PaymentType = 'Amazon fees    Commission' 
                THEN Amount ELSE NULL END) AS AmazonCommission
     , MAX(CASE WHEN PaymentType = 'Amazon fees    Fixed closing fee' 
                THEN Amount ELSE NULL END) AS AmazonFixedClosingFee
     , MAX(CASE WHEN PaymentType = 'Amazon fees    Variable closing fee' 
                THEN Amount ELSE NULL END) AS AmazonVariableClosingFee
     , MAX(CASE WHEN PaymentType = 'Other        Shipping        ' 
                THEN Amount ELSE NULL END) AS Shipping
     , Product Title
  FROM ...
GROUP
    BY Date
     , OrderID
     , SKU
     , Product Title

i might not have gotten the field names or values right, but at least you can see the technique for collapsing many rows to one and creating specific columns...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Yes, my local system where the MySQL server is running is Linux (Ubuntu) and I'll take a look at AWK.

In the meantime, I tried r937's SELECT statement and it seems to work perfectly! A couple values are empty but that should be easy for me to get sorted out, and even if I cannot translate the fields to exactly that of the table to ultimately hold it along with other data, it still saves tremendous time of doing it all manually. Thanks to both of you for your kind help.

Don P

 

[PCHomepage]

All the values are now working with fully populated fields with only some slight tweeking:

SELECT Date
     , OrderID
     , Quantity
     , MAX(CASE WHEN PaymentType LIKE '%Charges%'
                THEN Amount ELSE NULL END) AS ProductCharges
     , MAX(CASE WHEN PaymentType LIKE '%fees%' AND PaymentDetails LIKE '%Commission%'
                THEN Amount ELSE NULL END) AS Commission
     , MAX(CASE WHEN PaymentType LIKE '%fees%' AND PaymentDetails LIKE '%Fixed%'
                THEN Amount ELSE NULL END) AS FixedClosingFee
     , MAX(CASE WHEN PaymentType LIKE '%fees%' AND PaymentDetails LIKE '%Variable%'
                THEN Amount ELSE NULL END) AS VariableClosingFee
     , MAX(CASE WHEN PaymentType LIKE '%Other%' AND PaymentDetails LIKE '%Shipping%'
                THEN Amount ELSE NULL END) AS Shipping
     , ProductTitle
  FROM amazon_temp
GROUP
    BY Date
     , OrderID

However, I need to SUM the three fee amounts rather than having them appear individually so I tried adding a subquery to do it but I'm not sure how to make it gives results from the same OrderID. As shown below, it gives no errors but it seems to be giving the sum of all the orders' fees from the entire table, which is what I expected:

SELECT Date
     , OrderID
     , Quantity
     , (SELECT SUM(Amount) FROM amazon_temp WHERE PaymentType LIKE '%fees%') AS Fees
     , MAX(CASE WHEN PaymentType LIKE '%Charges%'
                THEN Amount ELSE NULL END) AS ProductCharges
     , MAX(CASE WHEN PaymentType LIKE '%Other%' AND PaymentDetails LIKE '%Shipping%'
                THEN Amount ELSE NULL END) AS Shipping
     , ProductTitle
  FROM amazon_temp
GROUP
    BY Date
     , OrderID

I tried using an alias for OrderID (OrderNo) inside the subquery and it gives more reasonable results but they are still not right:

SELECT Date
     , OrderID AS OrderNo
     , Quantity
     , (SELECT SUM(Amount) FROM amazon_temp WHERE PaymentType LIKE '%fees%' AND OrderID = OrderNo) AS Fees
     , MAX(CASE WHEN PaymentType LIKE '%Charges%'
                THEN Amount ELSE NULL END) AS ProductCharges
     , MAX(CASE WHEN PaymentType LIKE '%Other%' AND PaymentDetails LIKE '%Shipping%'
                THEN Amount ELSE NULL END) AS Shipping
     , ProductTitle
  FROM amazon_temp
GROUP
    BY Date
     , OrderID

Can someone suggest something to tidy up this last issue?

 

[r937]

like this?

SELECT Date
     , OrderID
     , Quantity
     , [red]SUM(CASE WHEN PaymentType LIKE '%fees%'  
                THEN Amount ELSE NULL END) AS Fees[/red]
     , MAX(CASE WHEN PaymentType LIKE '%Charges%'  
                THEN Amount ELSE NULL END) AS ProductCharges 
     , MAX(CASE WHEN PaymentType LIKE '%Other%'
                 AND PaymentDetails LIKE '%Shipping%'   
                THEN Amount ELSE NULL END) AS Shipping 
     , ProductTitle
  FROM amazon_temp
GROUP
    BY Date
     , OrderID
     , Quantity
     , ProductTitle

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Oops! No need for additional help unless there is a simplification to the subquery. The bogus amounts for the Fees came from the entries in the table being there twice. Apparently the CSV file I used for importing the data had gotten messed up but without the duplicates, the Fee values are exactly what they should be. Thanks again for their help.

Don P

 

[PCHomepage]

Ah, that's much better. I knew that actually (now that I see it, of course) but am a bit rusty as I haven't been programming lately. Thanks a bundle.

Don P

 

[PCHomepage]

In case it helps anyone with a similar issue, here is what I ended up with to do the query and insert the results into another table. I fudged a little on three of the fields and maybe there was a better way but I needed to get it done. They insert some static values into three fields so I ran one of the CASE conditionals three times where I knew it would return nothing and substituted the values there. I also changed the aliases to match the destination table just to make it a little clearer and to avoid mistakes:

INSERT INTO amazon (Date, TransactionID, Fee, Gross, ShippingHandling, AuctionSite, Status, Type, ItemTitle, ItemID)
SELECT Date
     , OrderID AS TransactionID
     , SUM(CASE WHEN PaymentType LIKE '%fees%'  
                THEN Amount ELSE NULL END) AS Fee
     , MAX(CASE WHEN PaymentType LIKE '%Charges%'
                THEN Amount ELSE NULL END) AS Gross
     , MAX(CASE WHEN PaymentType LIKE '%Other%' AND PaymentDetails LIKE '%Shipping%'
                THEN Amount ELSE NULL END) AS ShippingHandling
     , MAX(CASE WHEN PaymentDetails = ''
                THEN PaymentDetails ELSE 'Amazon Sale' END) AS AuctionSite
     , MAX(CASE WHEN PaymentDetails = ''
                THEN PaymentDetails ELSE 'Completed' END) AS Status
     , MAX(CASE WHEN PaymentDetails = ''
                THEN PaymentDetails ELSE 'Amazon Payment Received' END) AS Type
     , ProductTitle AS ItemTitle
     , SKU AS ItemID
  FROM amazon_temp
WHERE OrderID NOT IN (SELECT TransactionID FROM amazon)
GROUP
    BY Date
     , OrderID