How to pivot column values into columns?

Question

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.

Example 1

Sample data:

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 

Desired output 1 - Pivot by Class, aggregate by Name

Name     Chinese     English   Biology  Maths
=======  ==========  ========  =======  ======
Nick     80          70        85       85
Kent     80          70        85       90

Note:

In my head i imagine the syntax would be:

SELECT Score FROM Scores
GROUP BY Name
PIVOT BY Class

Desired output 2 - Pivot by Class, aggregate Score

Name     Chinese     English   Biology  Maths
=======  ==========  ========  =======  ======
70                   Nick
70                   Kent
80       Nick        
80       Kent             
85                             Nick     Nick
85                             Kent
90                                      Kent

Note:

In my head i imagine the syntax would be:

SELECT Name FROM Scores
GROUP BY Score
PIVOT BY Class

Desired output 3 - Pivot by Score, aggregate by Name

Name     70          80        85       90
=======  ==========  ========  =======  =====
Nick     English     Chinese   Biology  
Nick     English     Chinese   Maths
Kent     English     Chinese   Biology  Maths

Note:

In my head i imagine the syntax would be:

SELECT Class FROM Scores
GROUP BY Name
PIVOT BY Score

Desired output 4 - Pivot by Score, aggregate by Class

Class    70          80        85       90
=======  ==========  ========  =======  =====
Chinese              Nick
Chinese              Kent
English  Nick
English  Kent
Biology                        Nick
Biology                        Kent
Maths                          Nick     Kent

In my head i imagine the syntax would be:

SELECT Name FROM Scores
GROUP BY Class 
PIVOT BY Score

Desired output 5 - Pivot by Name, aggregate by Class

Class    Nick  Kent
=======  ====  ====
Chinese  80    80
English  70    70
Biology  85    85
Maths    85    90

In my head i imagine the syntax would be:

SELECT Score FROM Scores
GROUP BY Class
PIVOT BY Name

Desired output 6 - Pivot by Name, aggregate by Score

Score  Nick     Kent
=====  =======  =======
70     English  English
80     Chinese  Chinese
85     Biology  Biology
85     Maths    Biology
90              Maths

In my head i imagine the syntax would be:

SELECT Class FROM Scores
GROUP BY Score
PIVOT BY Name

Note: i don't want a single 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.

Another Example Set

Another example might be parsing a log of user's logging into the domain:

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

Desired output 7 - Pivot by date portion of LoginDate, aggregate by Username:

Username  20120901   20120902  20120903  20120914
========  =========  ========  ========  ========
iboyd     obsidian   obsidian  obsidian  harpax
iboyd     obsidian   harpax    obsidian  harpax
nbach     president  president
nback     nichpc     president

In my head i imagine the syntax would be:

SELECT MachineName FROM Logins
GROUP BY Username
PIVOT BY CONVERT(varchar(50), LoginDate, 112) --yyyymmdd format

Or perhaps:

SELECT MachineName FROM Logins
GROUP BY Username
PIVOT BY CAST(LoginDate AS DATE)

i just can't seem to wrap my head out the PIVOT syntax; in order to tell SQL Server what column values should become columns, and what column values the aggregation happens over.

Everyone seems to want to hard-code the columns, or invokes some XML queries. i just want to do a pivot!

---

See also

• Pivot SQL query - Show Date in column
• https://stackoverflow.com/questions/tagged/pivot+sql-server
• How to Pivot a Table?
• SQL Fiddle example

---

The Real Question^TM

The real problem i'm trying to solve today is the screenshot mockup that was given to me by the "business":

Which could be a fairly obvious query to write, if the SQL Server syntax were fairly obvious to me:

SELECT 
    JobName, ShiftName, 
    Firstname+' '+Lastname+' - '+BankCode
FROM Transactions
GROUP BY JobName, ShiftName
PIVOT BY TransactionDate

Answer 1

Think of the pivot operator as replacing your group by. Here is an example for your sample #1 and 3:

SELECT name, [Chinese], [English], [Biology], [Maths]
FROM scores s
PIVOT (
    SUM(score) 
    FOR Class IN ([Chinese], [English], [Biology], [Maths])
) p

SELECT name, [70], [80], [85], [90]
FROM scores s
PIVOT (
    MAX(class) 
    FOR score IN ([70], [80], [85], [90])
) p