title: Pivot table for string in SQL Server
body: <p>I have the following table with some details as shown below: </p>
  
  <p><strong>Example</strong>:</p>
  
  <p>Creating table <code>product</code>:</p>
  
  <pre><code>create table product
  (
    slno int,
    item nvarchar(20)
  );
  </code></pre>
  
  <p>Inserting some records:</p>
  
  <pre><code>insert into product values(1,'HDD');
  insert into product values(2,'PenDrive');
  insert into product values(3,'RAM');
  insert into product values(4,'DVD');
  insert into product values(5,'RAM');
  insert into product values(6,'HDD');
  </code></pre>
  
  <p>Table product contains:</p>
  
  <pre><code>select * from product;
  
  slno  item
  ---------- 
   1    HDD
   2    PenDrive
   3    RAM
   4    DVD
   5    RAM
   6    HDD
  </code></pre>
  
  <p>Now I want make a string of items for which i have written the following script:</p>
  
  <pre><code>select distinct 
  (
     select distinct item+','
     from product
     FOR XML PATH('')
  ) temp
  from product;
  </code></pre>
  
  <p>Result is:</p>
  
  <pre><code> temp
  ----------------------
  DVD,HDD,PenDrive,RAM,
  </code></pre>
  
  <p><strong>Note</strong>: Now I want to show the result in the following format: (In which I need to use the <code>pivot table</code> with the above query and need to display how many product have sold out).</p>
  
  <pre><code>DVD  HDD  PenDrive  RAM
  -----------------------
   1    2      1       2
  </code></pre>
  
  <p><strong>My Try</strong>:</p>
  
  <pre><code> select DVD,HDD,PenDrive,RAM
   from
   (
        select distinct 
        (
           select distinct item+','
           from product
           FOR XML PATH('')
        ) temp 
   from product
   ) as a
   pivot 
   (
      count(temp)
      for temp in(DVD,HDD,PenDrive,RAM)
   ) pt
  </code></pre>
  
  <p>But getting result :</p>
  
  <pre><code>DVD  HDD  PenDrive  RAM
  ------------------------
   0    0      0       0
  </code></pre>