Pivot Data based on Unknown Field Items

				
--**************************************
-- Name: Pivot Data based on Unknown Field Items
-- Description:I was posed a question to pivot data on a column without knowing exactly what values exist.
-- By: James Travis
--
-- Inputs:You need to change so fields but they are noted by name of what they do.
--
-- Returns:A pivot table based on values you did not have to know.
--
-- Assumes:There is a downside to this in that the query so far can be only 8000 characters at most I am looking at other was to increase this by using multiple variables then combine in execute but have not gotten to test yet.
--
-- Side Effects:Depending on the number of unknowns it will crash if it is larger than 8000 characters of data. Also as this uses a cursor it can be slow so test it before you use in production. You may just want to use to build the query once a day and output to someplace it can be used from.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=322&lngWId=5--for details.--**************************************

DECLARE @SQLState VARCHAR(8000) --This is where your sql string will be built
DECLARE @FieldPivotBasedOn VARCHAR(100) --This will hold each value when we pull from cursor
/* No comma's here as we may have no products and we want each new output to 
* add its own , so we do not have to cut the last character off if when we loop thru.
*/
Set @SQLState = 'SELECT FieldColumnsAreFrom'
Print (@SQLState)
/* We are getting all the possible values for Product elimating duplicates. */
DECLARE cur_Cases CURSOR FOR SELECT DISTINCT FieldPivotBasedOn FROM tblUse 
OPEN cur_Cases --Open the cursor
/* Get the next value from the cursor and put in variable. */
FETCH NEXT FROM cur_Cases INTO @FieldPivotBasedOn
WHILE @@FETCH_STATUS = 0 --As Long as we got data keep going.
BEGIN
/* Each time thru we will add another product as a possiblity for this pivot. */
SET @SQLState = @SQLState + ', SUM(CASE FieldPivotBasedOn WHEN ''' + @FieldPivotBasedOn + ''' THEN ValueIfCase ELSE ValueIfNotCase END) as [' + @FieldPivotBasedOn + ']'
/* Get the next value from the cursor and put in variable. */
FETCH NEXT FROM cur_Cases INTO @FieldPivotBasedOn
END 
CLOSE cur_Cases --We no longer need cursor so close
DEALLOCATE cur_Cases --and free memory
SET @SQLState = @SQLState + ' FROM tblUse GROUP BY FieldColumnsAreFrom'
--Print (@SQLState) --This line is commented out, just uncomment to output the query this built for debugging.
EXEC (@SQLState) --This line will execute the sql statement we built in @SQLState, add -- to front to comment out.