title: SQL Server : Split Columns to Count Totals By Year in same row
body: <p>T-SQL question: I have a breakdown of the visits by year of a person. I want to get a count of the total visits per year and then create a column for each year to summarize the total. The way I have it now it only returns the totals per year for the columns on separate rows. How can I combine them?</p>
  
  <p>Example temp table and code...</p>
  
  <pre><code>CREATE TABLE #Events            
  (
       Col1 int PRIMARY KEY,
       Person Varchar(20),
       VisitYear INT,
       VisitInfo Varchar(20)
  )           
  
  INSERT INTO #Events 
  VALUES (1, 'User1', '2017', 'Combo'), (2, 'User1', '2017', 'Special'),
         (3, 'User1', '2018', 'ComboBig'), (4, 'User2', '2017', 'Special'),
         (5, 'User2', '2017', 'ComboBig'), (6, 'User2', '2018', 'ComboBig'),
         (7, 'User2', '2018', 'Special'), (8, 'User2', '2018', 'Special2'),
         (9, 'User3', '2018', 'Combo')            
  
  SELECT DISTINCT 
      Person,
      VisitYear,
      VisitInfo,
      COUNT(Person) OVER(PARTITION BY Person, VisitYear) AS TtlPerYear 
  INTO 
      #EventCountPerYear
  FROM 
      #Events E
  
  SELECT * 
  FROM #EventCountPerYear
  
  SELECT DISTINCT 
      E1.Person,
      CASE WHEN E1.VisitYear IN ('2017') THEN E1.TtlPerYear END AS '2017_Visits',
      CASE WHEN E1.VisitYear IN ('2018') THEN E1.TtlPerYear END AS '2018_Visits'
  FROM 
      #EventCountPerYear E1
  </code></pre>
  
  <p>Right now the results comes split with the Year counts on separate rows. Is there a way to have it result in just one clean row of data?</p>
  
  <p>Images of current and desired results listed below...</p>
  
  <p>The current results:</p>
  
  <p><a href="https://i.stack.imgur.com/n69I1.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/n69I1.jpg" alt="enter image description here"></a></p>
  
  <p>Desired results]</p>
  
  <p><a href="https://i.stack.imgur.com/h97OY.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/h97OY.jpg" alt="enter image description here"></a></p>