Flatten Table Select Query

  • I have a table that has two columns: ItemNmbr, ListPrce. The data in the table is like this:

    A2320C, 100.00

    A2320C Core, 10.00

    C15310C, 100.00

    C15310C Core, 10.00

    K2640150.00

    K2641175.00

    I need to do a select query to get the results like this:

    ItemNmbr, ListPrce, CorePrce

    A2320C, 100.00, 10.00

    C15310C, 100.00, 10.00

    K2640, 150.00, Null

    K2641, 175.00, Null

    I've tried the following query, but it doesn't return items that don't have "Core" after it:

    Select i.ItemNmbr, i.ListPrce, i2.ListPrce

    FROM IV00105 AS i

    INNER JOIN IV00105 AS i2 on i.itemNmbr = LEFT(i2.ItemNmbr, LEN(i2.ItemNmbr) - 5)

    WHERE i2.ItemNmbr like '%Core' and i.ItemNmbr not like '%Core'

    I've tried the following query, but it returns Null for every CorePrce:

    SELECT i.ItemNmbr, i.ListPrce,

    (SELECT i1.ListPrce

    FROM IV00105 AS i1

    INNER JOIN IV00105 as i2 on i1.ItemNmbr = i2.ItemNmbr

    WHERE i.ItemNmbr = i1.ItemNmbr AND i2.ItemNmbr like '%Core') AS CorePrice

    FROM IV00105 AS i

    WHERE i.ItemNmbr NOT LIKE '%Core'

    I've tried this query, but I get an error "Invalid length parameter passed to the SUBSTRING function"

    SELECT i.ItemNmbr, i.ListPrce,

    (SELECT i1.ListPrce

    FROM IV00105 AS i1

    WHERE i.ItemNmbr = LEFT(i1.ItemNmbr, LEN(i1.ItemNmbr)-5)) AS CorePrice

    FROM IV00105 AS i

    And I've tried many more queries, but can't seem to get it right. Can anyone help me with this?

    Thanks,

    Wendy Schuman


    Wendy Schuman

  • try this

    SELECT i.ItemNmbr, i.ListPrce,i1.ListPrce

    FROM IV00105 AS i left join (SELECT ItemNmbr, ListPrce FROM IV00105 AS i1 WHERE  i1.ItemNmbr like '%Core') AS i1

    on i.ItemNmbr=left(i1.ItemNmbr,len(i1.ItemNmbr)-5)

    WHERE i.ItemNmbr NOT LIKE '%Core'

     

  • That did it. Thanks so much.


    Wendy Schuman

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply