Please visit our sponsor
UNKNOWN --************************************** -- 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/xq/ASP/txtCodeId.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 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.