Update:
Here is a link to a much better explanation of Pivot Tables
Original post 2012.04.19:
I continue to get questions on how to generate pivot tables. Although the concept seems complicated, the process can actually be quite simple.
Say I have data that looks like this:
and I want it to look like this:
You can accomplish with using a pivot query.
First, let's look at the SQL that generates the data set you are working with.
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status, --this will be out pivot column
(MODIFDT + Modified_Time) as ModDateTime
FROM
SV30301
WHERE
WS_Appointment_ID > 3000
The first step to pivoting the data is to clearly define the base query (above). Any concatenations or case statements need to be done here. It is important that you identify which column you will pivot on. That is the column you want to group horizontally. In this case, it is the status. You will then need to identify all possible values in this column.
BROADCAST
RECEIVED
ACCEPTED
IN ROUTE
ON SITE
PARTS HOLD
COMPLETE
These values will become your column names.
Next, you should determine what data you want to aggregate by these groupings. Any aggregate function applies here, so you can use functions like SUM, AVG, MAX, and COUNT. (see full list of aggregate functions) In this case, we want to get the max date for each status.
So we first change our original query to prepare for adding the pivot logic:
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician
FROM ( SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status,
(MODIFDT + Modified_Time) as ModDateTime
FROM
SV30301
WHERE
WS_Appointment_ID > 3000
) AS source
Notice that we did not include the pivot column or aggregate column. Those two columns will before a single column in the next step.
Next, we add the pivot logic:
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
ISNULL(BROADCAST,GETDATE()) AS BROADCAST,
RECEIVED,
ACCEPTED,
[IN ROUTE],
[ON SITE],
[PARTS HOLD],
COMPLETE,
UNASSIGNED
FROM (
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status,
(MODIFDT + Modified_Time) as ModDateTime
FROM
dbo.SV30301
) AS source
PIVOT(
MAX(ModDateTime)
FOR Appointment_Status
IN (
[BROADCAST],
[RECEIVED],
[ACCEPTED],
[IN ROUTE],
[ON SITE],
[PARTS HOLD],
[COMPLETE],
[UNASSIGNED]
)
) as pvt
In the pivot code, we first perform the aggregate operation on the aggregate column. We issue the FOR keyword followed by the pivot column and then the list of all possible values in the pivot column. Each value should be surrounded by [] (square brackets). We then list all of those values as column names in the upper select statement. Standard select logic applies here. You can rename the columns at this point or perform other operations on them.
--------------------------------------
Update 20150112:
So the above method is great if you already know the values that are in the columns. But what if you don't know the values and still need to pivot. In that case, you will need to dynamically build the query. That means you will need to use a stored proc instead of a view.
--recall that we are pivoting on the appointment status
--first we need to declare a variable and fill it with the possible values from the table
DECLARE @STATUS VARCHAR(MAX)
SELECT @STATUS=(SELECT DISTINCT '[' + RTRIM(Appointment_Status) + '],' FROM dbo.SV30301 WHERE RTRIM(Appointment_Status)!='' FOR XML PATH(''))
SELECT @STATUS=SUBSTRING(@STATUS,1,LEN(@STATUS)-1)
--now @STATUS will replace the "IN" portion and some of the fields in the select
--we must place the query in a variable as a string and then execute it
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL='SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,' + @STATUS + ' FROM (
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status,
(MODIFDT + Modified_Time) as ModDateTime
FROM
dbo.SV30301
) AS source
PIVOT(
MAX(ModDateTime)
FOR Appointment_Status
IN (' + @STATUS + ')
) as pvt'
EXEC (@SQL)
--that's it.
Did this help you? If so, please leave a comment!
