Pivot data - with dynamic headers

[ScottLyon]

I'm in the process of writing a stored procedure to read a bunch of data, and then pivot it around to import into a report.

At this point, the data is in a temporary table such as this:

DECLARE @unit_category_table table
(
unit_number varchar(50),
category varchar(50),
sort_order int,
TotalQty float
)

In this case, the "category" column is the actual name of the category, and I need a grid where the rows are the units, and the columns are each (and every) unique value of "category". The data itself is the sum of the TotalQty column.

At times, here may be only 5 columns, and 15 at others.

And, to complicate matters further, I have a "sort_order" column to determine in what order those columns should go (1=first, 2=second, etc., and 0=omit column entirely).


But I'm having very little luck figuring out how to pivot that data around as I want.


How do I do what I'm asking?


Thanks!


P.S. For what it's worth, we're running MS SQL 2005.