on 10-18-2008 8:23 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.