Skip to Content
avatar image
Former Member

Pricelist column wise

Hi,

When I develop a query with tables ITM1 and OPLN , it returns me the different pricelist like purchase price , sales price etc in for a particular item in different rows.I want it to return the output where for a single item it should return the pricelist columnwise.

Pls advise how to do it.

Rgds,

Rajeev

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 18, 2008 at 07:52 AM

    Rajeev,

    This is how you would do it. The pricelist name cannot be dynamically used as column names but has to be manually entered.

    SELECT DISTINCT T0.ItemCode AS 'Item No.', 
    (SELECT Price FROM ITM1 WHERE Pricelist = 1 AND ItemCode = T0.ItemCode) [Base Price],
    (SELECT Price FROM ITM1 WHERE Pricelist = 3 AND ItemCode = T0.ItemCode) [Wholesale Price]
    ..
    ..
    ..
    FROM  [dbo].[ITM1] T0

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      Please help me how we can display alternate items column wise .Also we would like to display the stock qty of alternate items .

      Thanks and Rgds,

      Rajeev

  • avatar image
    Former Member
    Oct 20, 2008 at 06:53 PM

    Hi Rajeev,

    The alternate items are not actual items but a link with another item by matching percentage. It will be no way to reach what you need if you do not tell us what would be the results look like in your mind.

    In another words, would you like to just list one item with its alternate items in one report or you want them all? If you choose later, it may have all duplicate items and may not be possible to produce any query results at all.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded