title: Selecting data within multiple DATE (year) ranges into named columns SQL Server
body: <p>I have to select data from a SQL Sever table for multiple date range i.e. </p>
  
  <pre><code>1990-1994,  1992-1996,  1994-1998,  1996-2000,  1998-2002,  2000-2004,  
  2002-2006,  2004-2008,  2006-2010,  2008-2012,  2010-2014  
  </code></pre>
  
  <p>I have used this query to get data without DATE ranges i.e. </p>
  
  <pre><code>SELECT 
      aid, research_area_category_id, 
      CAST(research_area as VARCHAR(100)) [research_area],
      COUNT(*) [Counting]
  FROM 
      sub_aminer_paper
  GROUP BY 
      CAST(research_area as VARCHAR(100)), aid, research_area_category_id
  HAVING 
      aid = 12403 
  ORDER BY 
      Counting DESC
  </code></pre>
  
  <p>This gives output as in image i.e. </p>
  
  <p><a href="https://i.stack.imgur.com/7vgcp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7vgcp.png" alt="enter image description here"></a>  </p>
  
  <p>Now for each DATE range using <code>WHERE</code> clause, I have to show data in respective column for DATE ranges. Whereas I have used this query i.e.  </p>
  
  <pre><code>SELECT 
      aid, research_area_category_id, 
      [research_area] = CAST(research_area as VARCHAR(100)), 
      [Counting] = COUNT(*),
      [1990 - 1994] = SUM(CASE WHEN p_year BETWEEN 1990 AND 1994 THEN 1 ELSE 0 END),
      [1992 - 1996] = SUM(CASE WHEN p_year BETWEEN 1992 AND 1996 THEN 1 ELSE 0 END),
      [1994 - 1998] = SUM(CASE WHEN p_year BETWEEN 1994 AND 1998 THEN 1 ELSE 0 END),
      [1996 - 2000] = SUM(CASE WHEN p_year BETWEEN 1996 AND 2000 THEN 1 ELSE 0 END),
      [1998 - 2002] = SUM(CASE WHEN p_year BETWEEN 1998 AND 2002 THEN 1 ELSE 0 END),
      [2000 - 2004] = SUM(CASE WHEN p_year BETWEEN 2000 AND 2004 THEN 1 ELSE 0 END),
      [2002 - 2006] = SUM(CASE WHEN p_year BETWEEN 2002 AND 2006 THEN 1 ELSE 0 END),
      [2004 - 2008] = SUM(CASE WHEN p_year BETWEEN 2004 AND 2008 THEN 1 ELSE 0 END),
      [2006 - 2010] = SUM(CASE WHEN p_year BETWEEN 2006 AND 2010 THEN 1 ELSE 0 END),
      [2008 - 2012] = SUM(CASE WHEN p_year BETWEEN 2008 AND 2012 THEN 1 ELSE 0 END),
      [2010 - 2014] = SUM(CASE WHEN p_year BETWEEN 2010 AND 2014 THEN 1 ELSE 0 END)
  FROM 
      sub_aminer_paper
  WHERE 
      aid = 2937  
      AND p_year BETWEEN 1990 AND 2014            
  GROUP BY
      aid, CAST(research_area AS VARCHAR(100)), research_area_category_id
  ORDER BY aid ASC, Counting DESC
  </code></pre>
  
  <p>And this query outputs this:  </p>
  
  <p><a href="https://i.stack.imgur.com/xLbya.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xLbya.png" alt="enter image description here"></a></p>
  
  <p>but I need <code>research_area_category_id</code> value under (1990-1994, 1992-1996, 1994-1998.....so on) these columns. E.g. in <code>1990 - 1994</code> column, it should display respective <code>research_area_category_id</code> i.e. <code>1</code>, <code>1</code> and <code>32</code> instead of <code>Counting</code> i.e. <code>1</code>, <code>1</code> and <code>1</code>, similarly it should display <code>33</code> instead of <code>2</code> in <code>1998 - 2002</code> column and vice versa.</p>
  
  <p>Please help and thanks in advance.</p>