--**************************************
--     
-- Name: Pivot Data based on Unknown Fie
--     ld 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 b
--     ut they are noted by name of what they d
--     o.
--
-- Returns:A pivot table based on values
--      you did not have to know.
--
-- Assumes:There is a downside to this i
--     n that the query so far can be only 8000
--      characters at most I am looking at othe
--     r was to increase this by using multiple
--      variables then combine in execute but h
--     ave not gotten to test yet.
--
-- Side Effects:Depending on the number 
--     of unknowns it will crash if it is large
--     r than 8000 characters of data. Also as 
--     this uses a cursor it can be slow so tes
--     t it before you use in production. You m
--     ay just want to use to build the query o
--     nce 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/xq/ASP/txtCod
--     eId.322/lngWId.5/qx/vb/scripts/ShowCode.
--     htm--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 comme
--     nted out, just uncomment to output the q
--     uery this built for debugging.
EXEC (@SQLState) --This line will EXECUTE the sql statement we built in @SQLState, ADD -- TO front to comment out.