SQL Queries - Creating a Grid from Three Columns

Question

My SQL table has three columns:

TaskID   VarID   Hours
001      001     10
001      002     40
001      003     100
002      001     50
002      002     80
002      003     90

I want to produce output like the following

TaskID     VarID ->   001      002      003
001                   10       40       100
002                   50       80       90

I think there is some query or function that can help me do this. Any ideas?

Answer 1

You need a cross-tab query. Unfortunately, this isn't easy to do in SQL Server. It's very easy in MS Access (there's a wizard to help.)

You'll need to use the PIVOT function. I'm using a stored proc like this:

   CREATE PROCEDURE [dbo].[usp_pivot_sa_byHomeCare] AS
   DECLARE @columns VARCHAR(1000)

   SELECT @columns = COALESCE(@columns + ',[' + cast(HomeCareRating as varchar) + ']',
   '[' + cast(HomeCareRating as varchar)+ ']')
   FROM vw_sa_byHomeCare
   GROUP BY HomeCareRating
   --print @columns

   DECLARE @query VARCHAR(8000)

   SET @query = '
   SELECT *
   FROM vw_sa_byHomeCare
   PIVOT
   (
   sum(count_pmin)
   FOR [HomeCareRating]
   IN (' + @columns + ')
   )
   AS p'

   --print @query

   EXECUTE(@query)

where my view (vw_sa_byHomeCare) is the view I want to pivot, HomeCareRating is the column to pivot over, and sum(count_pmin) is my value.

Answer 2

Yes, you can do this fairly easily in SQL Server 2005 and up - the magic word is PIVOT.

Here's the query you need:

SELECT 
      TaskID,
      [001], [002], [003]
FROM 
    YourTable
PIVOT 
    ( 
     SUM(HOURS)
     FOR VarID IN ([001], [002], [003])
    ) 
    AS YourPivot
ORDER BY 
      YourPivot.TaskID

The idea of a PIVOT table is a bit tricky to grok at first - maybe these additional sources of information will help:

• MSDN: Using PIVOT and UNPIVOT
• Pivot table for SQL Server
• SQL Server: Pivot table example

The explanation from MSDN might shed some light:

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. 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. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

So you basically take a variable VarID and turn its distinct values (001, 002, 003) and an aggregate over those (SUM(Hours)) into columns of a new "pivot" table.

Marc