title: Pivoting and partitioning Query to get Names
body: <p>I tried the HackerRank question below where the Names have to be pivoted on the Occupations.</p>
  
  <p><a href="https://www.hackerrank.com/challenges/occupations/problem" rel="nofollow noreferrer">https://www.hackerrank.com/challenges/occupations/problem</a></p>
  
  <p>I found a solution, but don't understand how it works specifically why are the other names returning in the result when taking a max on the names.</p>
  
  <p>Table:</p>
  
  <pre><code>CREATE TABLE Occupations
  (
      Name varchar(25),
      Occupation varchar(1000)
  )
  
  INSERT INTO Occupations VALUES('Samantha', 'Doctor')
  GO
  INSERT INTO Occupations VALUES('Julia', 'Actor')
  GO
  INSERT INTO Occupations VALUES('Maria', 'Actor')
  GO
  INSERT INTO Occupations VALUES('Meera', 'Singer')
  GO
  INSERT INTO Occupations VALUES('Ashley', 'Professor')
  GO
  INSERT INTO Occupations VALUES('Ketty', 'Professor')
  GO
  INSERT INTO Occupations VALUES('Christeen', 'Singer')
  GO
  INSERT INTO Occupations VALUES('Jane', 'Actor')
  GO
  INSERT INTO Occupations VALUES('Jenny', 'Doctor')
  GO
  INSERT INTO Occupations VALUES('Priya', 'Singer')
  GO
  </code></pre>
  
  <p>Solution:</p>
  
  <pre><code>SELECT [Doctor], [Professor], [Singer],[Actor] 
  FROM 
  (   
      SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 
      FROM Occupations) as tempTable 
      PIVOT 
      (
          MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
      ) AS pivotTable
  </code></pre>
  
  <p>So the way I understand this query gets executed is that the rows get partitioned by Occupation ordered by the name and then the max names gets pivoted on the Occupations.  So how does the query still return the other names by occupation?</p>