title: Show multiple rows into different columns in SQL
body: <p>I have below query which shows the product listing in different rows for the same product but different sale status:</p>
  
  <pre><code>select distinct productname as 'Product', sale_status as 'Sold/Unsold', count(distinct i.item_number) as 'Total Items'
              from item i
              left join department d on i.item_number = d.item_number
              where d.term = 'WINTER' and d.year = '2014'
              group by productname,sale_status
              order by productname
  </code></pre>
  
  <p>It gives the output as:</p>
  
  <pre><code>       Product       Sold/Unsold        Total Items
          Bags            SOLD                 20
          Bags            UNSOLD              100
          Shoes           SOLD                 30
          Shoes           UNSOLD               50
  </code></pre>
  
  <p>Now I need to show the output as:</p>
  
  <pre><code>       Product         SOLD        UNSOLD        Total Items
          Bags            20          100             120
          Shoes           30           50              80
  </code></pre>
  
  <p>I tried using the PIVOT to achieve this task like below but I am not being able to achieve this goal. It says: Incorrect syntax near the keyword 'PIVOT'.</p>
  
  <pre><code>select distinct productname as 'Product', sale_status as 'Sold/Unsold', count(distinct i.item_number) as 'Total Items'
              from item i
              left join department d on i.item_number = d.item_number
              where d.term = 'WINTER' and d.year = '2014'
              group by productname,sale_status
              order by productname
              PIVOT
              (
              SUM (Total Items)
              FOR [Sold/Unsold] IN ([SOLD], [UNSOLD])
              ) as P
  </code></pre>
  
  <p>Any suggestion or any other possible way to achieve this goal?</p>