title: How to pivot column values into columns?
body: <p>i've browsed through a lot of the SQL Pivot example on Stackoverflow, in the Books Online, and in google, and i still cannot figure out how to perform (what i would call) a simple pivot operation.</p>
  
  <h2><a href="https://stackoverflow.com/questions/4020690/how-to-make-column-to-row-without-an-aggregate-function-in-sql-server-2005-8">Example 1</a></h2>
  
  <p>Sample data:</p>
  
  <pre><code>Name     Class       Score
  =======  ==========  ======
  Nick     Chinese     80 
  Nick     English     70 
  Nick     Biology     85 
  Nick     Maths       85
  Kent     Chinese     80 
  Kent     Maths       90 
  Kent     English     70 
  Kent     Biology     85 
  </code></pre>
  
  <h2>Desired output 1 - Pivot by Class, aggregate by Name</h2>
  
  <pre><code>Name     Chinese     English   Biology  Maths
  =======  ==========  ========  =======  ======
  Nick     80          70        85       85
  Kent     80          70        85       90
  </code></pre>
  
  <p>Note: </p>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Score FROM Scores
  GROUP BY Name
  PIVOT BY Class
  </code></pre>
  </blockquote>
  
  <h2>Desired output 2 - Pivot by Class, aggregate Score</h2>
  
  <pre><code>Name     Chinese     English   Biology  Maths
  =======  ==========  ========  =======  ======
  70                   Nick
  70                   Kent
  80       Nick        
  80       Kent             
  85                             Nick     Nick
  85                             Kent
  90                                      Kent
  </code></pre>
  
  <p>Note: </p>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Name FROM Scores
  GROUP BY Score
  PIVOT BY Class
  </code></pre>
  </blockquote>
  
  <h2>Desired output 3 - Pivot by Score, aggregate by Name</h2>
  
  <pre><code>Name     70          80        85       90
  =======  ==========  ========  =======  =====
  Nick     English     Chinese   Biology  
  Nick     English     Chinese   Maths
  Kent     English     Chinese   Biology  Maths
  </code></pre>
  
  <p>Note: </p>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Class FROM Scores
  GROUP BY Name
  PIVOT BY Score
  </code></pre>
  </blockquote>
  
  <h2>Desired output 4 - Pivot by Score, aggregate by Class</h2>
  
  <pre><code>Class    70          80        85       90
  =======  ==========  ========  =======  =====
  Chinese              Nick
  Chinese              Kent
  English  Nick
  English  Kent
  Biology                        Nick
  Biology                        Kent
  Maths                          Nick     Kent
  </code></pre>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Name FROM Scores
  GROUP BY Class 
  PIVOT BY Score
  </code></pre>
  </blockquote>
  
  <h2>Desired output 5 - Pivot by Name, aggregate by Class</h2>
  
  <pre><code>Class    Nick  Kent
  =======  ====  ====
  Chinese  80    80
  English  70    70
  Biology  85    85
  Maths    85    90
  </code></pre>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Score FROM Scores
  GROUP BY Class
  PIVOT BY Name
  </code></pre>
  </blockquote>
  
  <h2>Desired output 6 - Pivot by Name, aggregate by Score</h2>
  
  <pre><code>Score  Nick     Kent
  =====  =======  =======
  70     English  English
  80     Chinese  Chinese
  85     Biology  Biology
  85     Maths    Biology
  90              Maths
  </code></pre>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT Class FROM Scores
  GROUP BY Score
  PIVOT BY Name
  </code></pre>
  </blockquote>
  
  <p><strong>Note:</strong> i don't want a <em>single</em> query that can perform all these pivots. i am using the sample data, and sample pivots, so use as examples of what pivots i might want to perform.</p>
  
  <h2><strong>Another Example Set</strong></h2>
  
  <p>Another example might be parsing a log of user's logging into the domain:</p>
  
  <pre><code>LoginDate          Username  MachineName
  =================  ========  ===========
  20120901 8:49:22   iboyd     obsidian
  20120901 9:10:19   nbach     president
  20120901 13:07:18  nback     nichpc
  20120902 8:58:38   iboyd     obsidian
  20120202 9:14:44   nbach     president
  20120902 18:34:43  iboyd     harpax
  20120903 8:57:13   iboyd     obsidian
  20120904 20:03:55  iboyd     harpax
  </code></pre>
  
  <h2>Desired output 7 - Pivot by date portion of LoginDate, aggregate by Username:</h2>
  
  <pre><code>Username  20120901   20120902  20120903  20120914
  ========  =========  ========  ========  ========
  iboyd     obsidian   obsidian  obsidian  harpax
  iboyd     obsidian   harpax    obsidian  harpax
  nbach     president  president
  nback     nichpc     president
  </code></pre>
  
  <blockquote>
    <p>In my head i imagine the syntax would be:</p>
  
  <pre><code>SELECT MachineName FROM Logins
  GROUP BY Username
  PIVOT BY CONVERT(varchar(50), LoginDate, 112) --yyyymmdd format
  </code></pre>
    
    <p>Or perhaps:</p>
  
  <pre><code>SELECT MachineName FROM Logins
  GROUP BY Username
  PIVOT BY CAST(LoginDate AS DATE)
  </code></pre>
  </blockquote>
  
  <p>i just can't seem to wrap my head out the <code>PIVOT</code> syntax; in order to tell SQL Server what column values should become columns, and what column values the aggregation happens over.</p>
  
  <p>Everyone seems to want to hard-code the columns, or invokes some XML queries. i just want to do a pivot!</p>
  
  <hr>
  
  <h2>See also</h2>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/4546433/pivot-sql-query-show-date-in-column">Pivot SQL query - Show Date in column</a></li>
  <li><a href="https://stackoverflow.com/questions/tagged/pivot+sql-server">https://stackoverflow.com/questions/tagged/pivot+sql-server</a></li>
  <li><a href="https://stackoverflow.com/questions/4857752/how-to-pivot-a-table">How to Pivot a Table?</a></li>
  <li><a href="http://sqlfiddle.com/#!3/6f4a1/3" rel="nofollow noreferrer">SQL Fiddle example</a></li>
  </ul>
  
  <hr>
  
  <h2>The Real Question<sup>TM</sup></h2>
  
  <p>The real problem i'm trying to solve today is the screenshot mockup that was given to me by the <em>"business"</em>:</p>
  
  <p><img src="https://i.stack.imgur.com/ogNRG.png" alt="enter image description here"></p>
  
  <p>Which could be a fairly obvious query to write, if the SQL Server syntax were fairly obvious to me:</p>
  
  <pre><code>SELECT 
      JobName, ShiftName, 
      Firstname+' '+Lastname+' - '+BankCode
  FROM Transactions
  GROUP BY JobName, ShiftName
  PIVOT BY TransactionDate
  </code></pre>