asked on

sql 2005 query

i have a table called customerproductinfo
where i have the data like this

SaleInvoiceID      GlobalProductID      SerialNumber      Priority      UnitPrice      UnitCost      AssociationNumber
46728      14      3234791127      2      0      0      1
46728      14      3234791181      7      0      0      4
46728      85      3234791127      4      335      0      1
46728      85      3234791181      9      335      0      4
46728      243            3      0      0      1
46728      243            8      0      0      4
46728      294      3234791181      11      110      0      7
46728      469      355751021386726      6      119.99      375      4
46728      507      359321020456973      1      50      160      1

is there any way i can ge the data in this format

saleinvoiceid      Globalproductid      serialnumber      unitprice      relatedglobalproductid      relatedserialnumber      runitcost      runitprice
46728      14      3234791127      0      0      0      0      0
46728      14      3234791181      0      0      0      0      0
46728      85      3234791127      335      507      359321020456973      160      50
46728      85      3234791181      335      469      355751021386726      375      119.99
46728      243            0      0      0      0      0
46728      243            0      0      0      0      0
46728      294      3234791181      110      0      0      0      0

Pavel Celba

Yes, but you need another table or column which contains relation between Globalproductid and relatedglobalproductid.

romeiovasu

ASKER

only associationnumber is reference key is there anyway we can write a store procedure to push the data into temp and make relation and get the output. in they way i have requested.

Pavel Celba

First two rows have associationnumber also but they are not associated probably because they don't have unitprice. Other problem is how to recognize what product to associate as related product. It could be product with higher globalproductid or product with longer serialnumber or something else? Please clarify.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial