Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2013
    Posts
    11

    Unanswered: MySQL Duplicate Rows

    I have an excel spreadsheet that I can enter into a table and manipulate far faster in MySQL. The issue is that the rows are Duplicated. The structure is shown below.

    Part Number | Description | Qty | Cost | OEM Resale
    ABC | Switch | 1 | 1.00 | 3.5
    ABC | Switch |25 | 1.00 | 2.75

    Desired Result
    Part Number | Description | Qty1 | Cost1 | OEM Resale1 | Qty2 | Cost2| Resale2

    I would like to have them in separate columns. I'm using phpmyadmin to write my querys. Is this at all possible? The best i've seen is the concat function which once i get past description would put all remaining data into one field that has been comma separated. I could do that and transfer back to excel.

    I ask as the file that I'm using is 30K+ rows and the coworker i'm setting this up for gets this file in the same format to evaluate about 5 times a year

    thanks

    c

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1

  3. #3
    Join Date
    Dec 2013
    Posts
    11
    This worked wonders, thanks so much

    Quote Originally Posted by spacebar View Post

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This worked wonders, thanks so much
    What was your exact/concrete query?

  5. #5
    Join Date
    Dec 2013
    Posts
    11
    looks a little something like this......

    SELECT
    mfgpn AS "EAO P/N",
    descrip as "Description",
    @brk1:=sum(qtybreak*(1-abs(sign(break-1)))) AS "Qty 1",
    @cost1:=sum(cost*(1-abs(sign(break-1)))) as "Cost 1",
    sum(oemrsl*(1-abs(sign(break-1)))) as "OEM Resale 1",
    @brk2:=sum(qtybreak*(1-abs(sign(break-2)))) AS "Qty 2",
    @cost2:=sum(cost*(1-abs(sign(break-2)))) AS "Cost 2",
    sum(oemrsl*(1-abs(sign(break-2)))) as "OEM Resale 2",
    @brk3:=sum(qtybreak*(1-abs(sign(break-3)))) AS "Qty 3",
    sum(cost*(1-abs(sign(break-3)))) AS "Cost 3",
    sum(oemrsl*(1-abs(sign(break-3)))) as "OEM Resale 3",
    @brk4:=sum(qtybreak*(1-abs(sign(break-4)))) AS "Qty 4",
    sum(cost*(1-abs(sign(break-4)))) AS "Cost 4",
    sum(oemrsl*(1-abs(sign(break-4)))) as "OEM Resale 4",
    @brk5:=sum(qtybreak*(1-abs(sign(break-5)))) AS "Qty 5",
    sum(cost*(1-abs(sign(break-5)))) AS "Cost 5",
    sum(oemrsl*(1-abs(sign(break-5)))) as "OEM Resale 5",
    @brk6:=sum(qtybreak*(1-abs(sign(break-6)))) AS "Qty 6",
    sum(cost*(1-abs(sign(break-6)))) AS "Cost 6",
    sum(oemrsl*(1-abs(sign(break-6)))) as "OEM Resale 6"
    FROM eao group by mfgpn

    Quote Originally Posted by tonkuma View Post
    What was your exact/concrete query?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The column names were not corresponding to your original post
    Part Number | Description | Qty | Cost | OEM Resale
    ABC | Switch | 1 | 1.00 | 3.5
    ABC | Switch |25 | 1.00 | 2.75
    Though, it is not my essencial issue,
    because I could guess the relationships of names in your original post(e.g. Part Number , Description) and your concrete query(e.g. mfgpn AS "EAO P/N", descrip as "Description") ,
    except break in (for example)
    @brk1:=sum(qtybreak*(1-abs(sign(break-1)))) AS "Qty 1",
    What is "break"?

  7. #7
    Join Date
    Dec 2013
    Posts
    11
    I did not want to post the entire request of what I needed initially as it was long as I have more to go. I simply felt the need to post enough information to get the help I needed in laying out the rest of my data.

    Quote Originally Posted by tonkuma View Post
    The column names were not corresponding to your original post


    Though, it is not my essencial issue,
    because I could guess the relationships of names in your original post(e.g. Part Number , Description) and your concrete query(e.g. mfgpn AS "EAO P/N", descrip as "Description") ,
    except break in (for example)
    Break simply refers to the column cost breaks that are a part of my data
    What is "break"?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Break simply refers to the column cost breaks that are a part of my data
    So, the column "cost breaks" must be in your data additional to your original example
    Part Number | Description | Qty | Cost | OEM Resale
    ABC | Switch | 1 | 1.00 | 3.5
    ABC | Switch |25 | 1.00 | 2.75
    What were the exact data of "cost breaks" in the example?
    I want to know the values of ??? in the following.
    Code:
    Part Number | Description | Qty | Cost | OEM Resale | cost breaks
    ABC         | Switch      | 1   | 1.00 | 3.5        | ???
    ABC         | Switch      |25   | 1.00 | 2.75       | ???

  9. #9
    Join Date
    Dec 2013
    Posts
    11
    Yes, the breaks are actually the column "qty". So at 1 piece the OEM resale is 3.5, at 25 pieces the OEM resale is 2.75, etc. I wanted to spread the "break" or qty out in a columnar format as opposed to row based format

    Quote Originally Posted by tonkuma View Post
    So, the column "cost breaks" must be in your data additional to your original example

    What were the exact data of "cost breaks" in the example?
    I want to know the values of ??? in the following.
    Code:
    Part Number | Description | Qty | Cost | OEM Resale | cost breaks
    ABC         | Switch      | 1   | 1.00 | 3.5        | ???
    ABC         | Switch      |25   | 1.00 | 2.75       | ???

Posting Permissions

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