Pricelist column wise

[Former Member]

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

[former_member583013 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Suda,

Similarly can I have the alternate items for a particular item columnwise instead of row wise.

Pls guide how.

Rgds,

Rajeev

[former_member583013 · Accepted solution]

The same Sub SELECT will not work or Alt Items. We need to handle this differently

[Former Member · Accepted solution]

Hi Suda,

Pls let me know how it can be handled fot alternate items

Rgds,

Rajeev

[Former Member · Accepted solution]

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

[Former Member]

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