title: Stored Procedure(Dynamic Columns in  SQL SERVER 2008)
body: <p>Im having 3 tables <code>Project</code> <code>departments</code> and <code>projectStatus</code> </p>
  
  <blockquote>
    <p>Department</p>
  </blockquote>
  
  <pre><code>ID          Name
  1         Software
  2           XML1
  .            .
  .            .
  </code></pre>
  
  <blockquote>
    <p>Project</p>
  </blockquote>
  
  <pre><code>ID       Name        DeptID   StatusID
  1        Prj1           1        1
  2        prj2           2        2
  .         .             .        .
  .         .             .        .
  </code></pre>
  
  <blockquote>
    <p>projectStatus</p>
  </blockquote>
  
  <pre><code>ID      Name
  1       InProgress
  2       Pending
  .         .
  .         .
  </code></pre>
  
  <p>I want Below combination:<br>
  <img src="https://i.stack.imgur.com/p3iJv.png" alt="enter image description here"></p>
  
  <p>I tried this</p>
  
  <pre><code>SELECT 
      Status=[Name],
      Software=(
          SELECT COUNT(*)
          FROM Projects 
          WHERE StatusID=PS.ID and DeptI =1
      ),
      XML1=( 
          SELECT COUNT(*)
          FROM Projects
          WHERE CurrentStatusID=PS.ID and DeptID=2
      ),
      Imaging=(
          SELECT COUNT(*)
          FROM Projects
          WHERE CurrentStatusID=PS.ID and DeptID =3
      )  
  FROM
      [MIS_STORE].[dbo].[M_ProjectStatuses] PS   
  </code></pre>
  
  <p>Here I want number of columns should retrieved dynamically depending number of departments.
  Thanks in advance... </p>
  
  <p><strong>Edit:Solution</strong></p>
  
  <pre><code>DECLARE @cols NVARCHAR(2000);
  DECLARE @query NVARCHAR(4000);
  
  
  SELECT  @cols = COALESCE(@cols + ',[' + Name + ']',
                           '[' + Name + ']')
  FROM    M_Departments 
  ORDER BY Name
  
  
  SET @query = 'SELECT [Status], '+
  @cols +'
  FROM
  (SELECT Projects.Name, 
  M_Departments.Name AS dept, 
  M_ProjectStatuses.Name AS [Status]
  FROM     Projects INNER JOIN
           M_Departments ON Projects.M_DeptID = M_Departments.ID INNER JOIN
           M_ProjectStatuses ON Projects.CurrentStatusID = M_ProjectStatuses.ID) test
   PIVOT
  (
  count(name)
  FOR dept IN
  ( '+ @cols +')) p '
  EXECUTE(@query)
  </code></pre>