Fun with Pivot tables

Update:

Here is a link to a much better explanation of Pivot Tables

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/

Wow, it has been almost a year since my last post!  Well, back to it.  This is an update to the original post below.  Additional info added to the end:

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!