title: SQL creating a pricing table
body: <p>Currently I have a table that consists of prices.</p>
  
  <p>Columns PriceList,ItemCode, DiscountLine,Amount,Price .</p>
  
  <p>E.G. </p>
  
  <pre><code>*1   , ITEM1 , 0 , 100 , 0.50  
  *1   , ITEM1 , 1 , 200 , 0.45  
  1234 , ITEM1 , 0 , 100 , 0.45  
  1234 , ITEM1 , 1 , 100 , 0.40  
  </code></pre>
  
  <p>The problem is, PriceList can either be a pricelist (preceded by *) or a customer number.</p>
  
  <p>I need the result in one line; </p>
  
  <p>so </p>
  
  <pre><code>CUSTOMER    ITEM    AMOUNT0   PRICE0    AMOUNT1    PRICE1
  1234        ITEM1   100       0.45      200        0.40
  </code></pre>
  
  <p>for this I am using the following code:</p>
  
  <pre><code>select Customer.CardCode ,Customer.CardName,SP.* from Customer inner join (
  select  ItemCode,CardCode,
     max(case when DPNum=0 then Amount end) Amount1,
     max(case when DPNum=0 then Price end) Price1,
     max(case when DPNum=1 then Amount end) Amount2,
     max(case when DPNum=1 then Price end) Price2,
     max(case when DPNum=2 then Amount end) Amount3,
     max(case when DPNum=2 then Price end) Price3,
     max(case when DPNum=3 then Amount end) Amount4,
     max(case when DPNum=3 then Price end) Price4,
     max(case when DPNum=4 then Amount end) Amount5,
     max(case when DPNum=4 then Price end) Price5,
     max(case when DPNum=5 then Amount end) Amount6,
     max(case when DPNum=5 then Price end) Price6,
     max(case when DPNum=6 then Amount end) Amount7,
     max(case when DPNum=6 then Price end) Price7,
     max(case when DPNum=7 then Amount end) Amount8,
     max(case when DPNum=7 then Price end) Price8,
     max(case when DPNum=8 then Amount end) Amount9,
     max(case when DPNum=8 then Price end) Price9,
     max(case when DPNum=9 then Amount end) Amount10,
     max(case when DPNum=9 then Price end) Price10
   from SPP2 group by ItemCode,CardCode 
   ) sp
   on sp.CardCode = Customer.CardCode or sp.CardCode = '*'+cast(ListNum as varchar(1))
   where Customer.Cardcode='1234'
   order by Customer.CardCode,ItemCode,sp.CardCode DESC
  </code></pre>
  
  <p>This works apart for one small detail;
  If a customer has a pricelist (and all customers do) AND they have "special" prices; I get 2 rows per item.</p>
  
  <p>so My result would be:</p>
  
  <pre><code>CUSTOMER    NAME    ITEM    PLCODE   AMOUNT0   PRICE0    AMOUNT1    PRICE1
  1234        DUMMY   ITEM1   1234     100       0.45      200        0.40
  1234        DUMMY   ITEM1   *1       100       0.50      200        0.45
  </code></pre>
  
  <p>If the customer has a special price, only this should be returned.
  is there any way to accomplish this in one query? (or maybe there is a more efficient way of doing what I am trying to accomplish)?</p>