So what is a pivot table you ask?
According to Microsoft it is defined as the following -
"PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output"
I am going to create some sample tables with data to show how to do this. We have will have two tables; People and Support. I want to find which catchers support which pitchers. In order to accomplish this I will use the famous pivot table in SQL 2005.
The People table is where all the people are listed with their information.
DECLARE @People TABLE ( PID VARCHAR(3) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL, EmployeeID INT NULL ) INSERT INTO @People VALUES (1, 'John','Axford',59)
INSERT INTO @People VALUES (2, 'Yovani','Gallardo',13)
INSERT INTO @People VALUES (3, 'Francisco','Rodriguez',57)
INSERT INTO @People VALUES (4, 'Zack','Greinke',13)
INSERT INTO @People VALUES (5, 'George','Kottaras',9)
INSERT INTO @People VALUES (6, 'Jonanthan','Lucroy',20)
The second table is titled support. This will show which catchers support which pitchers.
DECLARE @Support TABLE( SID INT, EmployeeID INT, SupportID INT
)
INSERT INTO @Support VALUES (1,59,20)
INSERT INTO @Support VALUES (3,59,9)
INSERT INTO @Support VALUES (2,57,20)
INSERT INTO @Support VALUES (5,57,9)
INSERT INTO @Support VALUES (4,13,9)
Now that we have all the data set up, lets start to dig into SQL!
First lets start off with a simple query to grab the pitcher information and catcher id.
SELECT P.FirstName + ' ' + P.LastName AS PitcherInformation, S.SupportID AS CatcherID, ROW_NUMBER() OVER(partition BY SupportID ORDER BY p.EmployeeID) AS row
FROM @People AS PINNER JOIN @Support AS S ON S.EmployeeID = p.EmployeeID
The row_number function will come in handy when we have to pivot this information.
Our second step will be to use the pivot function.
SELECT CatcherID, [1] AS Pitcher1, [2] AS Pitcher2, [3] AS Pitcher3
FROM (SELECT P.FirstName + ' ' + P.LastName AS PitcherInformation, S.SupportID AS CatcherID, ROW_NUMBER() OVER(partition BY SupportID ORDER BY p.EmployeeID) AS row
FROM @People AS PINNER JOIN @Support AS S ON S.EmployeeID = p.EmployeeID)a pivot (MAX(PitcherInformation) FOR row IN ([1],[2],[3])) AS pivottable
That is looking pretty good, but what we want to know the catcher rather than his ID, right? Our third step will be to join the People table again to grab that information.
SELECT CatcherID, [1] AS Pitcher1, [2] AS Pitcher2, [3] AS Pitcher3
FROM (SELECT P.FirstName + ' ' + P.LastName AS PitcherInformation, S.SupportID AS CatcherID, ROW_NUMBER() OVER(partition BY SupportID ORDER BY p.EmployeeID) AS row
FROM @People AS P
INNER JOIN @Support AS S ON S.EmployeeID = p.EmployeeID)a pivot (MAX(PitcherInformation) FOR row IN ([1],[2],[3])) AS pivottable
We did it! Here is a little bonus for you. Instead of having multi columns for pitchers let's just have one column. It's pretty simple.
SELECT p.FirstName + ' ' + p.LastName AS Catcher, ISNULL([1], '' ) + ISNULL(' , ' + [2],'') + ISNULL(' , ' + [3],'') AS Pitchers
FROM (SELECT P.FirstName + ' ' + P.LastName AS PitcherInformation, S.SupportID AS CatcherID, ROW_NUMBER() OVER(partition BY SupportID ORDER BY p.EmployeeID) AS row
FROM @People AS P
INNER JOIN @Support AS S ON S.EmployeeID = p.EmployeeID)a pivot (MAX(PitcherInformation) FOR row IN ([1],[2],[3])) AS pivottable
INNER JOIN @People AS P ON pivottable.CatcherID = p.EmployeeID
Well helpfully you learned to how to use the pivot function.
Let's go Brewers!
Robert
