Display individual prices on each row

Question

I am currently using MS SQL Studio 2017 and SAP B1. The table i am working with is called the ITM1 Table. Inside this table are the following fields...

ItemCode, PriceListNo, and Price.

Inside price list it is sequenced as follows... 1 2 3, 1 2 3, 1 2 3.

Each item has 3 identical item codes against it. And the price field contains £7.00, £14.00, £15.00 (for example 1 - Supplier Price, 2 - Base Price, 3 - Retail price) And the price list field varies dependent upon what price list number is selected.

I want to create a table which will display... "Item Code, Supplier Price, Base Price, Retail Price" (These are obviously WHERE PriceListNo = '1'

How do i get this to show in one table. I've been trying case statements this morning and had no luck, I've also tried sub queries and can't seem to fathom it?

I want to relay this information in the above table to a single ItemCode column, but display Price £14.50, £21, £21 £21 on an individual column Thanks for any help

Kind Regards Andy

Could you please give us better detailing. schema of your table, sample data and sample desired result — Muhammad Vakili, Sep 17 '18 at 12:35
What is the formula of Supplier Price, Base Price, Retail Price? — Ankit Bajpai, Sep 17 '18 at 12:43
I don't understand the data model. What is price list? It seems really important to your question. — Gordon Linoff, Sep 17 '18 at 12:53
[Here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great place to start. — Sean Lange, Sep 17 '18 at 14:01
Each item in this SQL database holds 8 prices against it. Only 4 of these are used... 1 = Supplier Price, 2 is base price, 3 is retail price, 4 is internet price. As standard these are displayed on individual rows as shown in the above image. I require 1 single Item code in column 1, then column 2 to display the supplier price, 3 to display base, 4 to display retail and 5 to display internet. — Andrew Gray, Sep 17 '18 at 14:05

Jim Jimson · Accepted Answer · 2018-09-17T14:45:21.343

There are a bunch of different ways to do this. One of which is using CASE like you alluded to;

CREATE TABLE dbo.ITM1
(
  ItemCode VARCHAR(64),
  PriceListNo INTEGER,
  Price DECIMAL(18,9)
);

INSERT INTO ITM1 VALUES  ('AHV16',1, 7.00),
                         ('AHV16',2, 14.00),
                         ('AHV16',3, 15.00),
                         ('AHV16',4, 18.00);

SELECT t.ItemCode,
SUM(CASE WHEN t.PriceListNo = 1 THEN t.Price END) [Supplier Price],
SUM(CASE WHEN t.PriceListNo = 2 THEN t.Price END) [Base Price],
SUM(CASE WHEN t.PriceListNo = 3 THEN t.Price END) [Retail Price],
SUM(CASE WHEN t.PriceListNo = 4 THEN t.Price END) [Internet Price]
FROM dbo.ITM1 t
GROUP BY t.ItemCode

Display individual prices on each row

1 Answers1