March 7, 2002 at 9:24 am
This is an extention to what is already posted for displaying horizontally data that is stored vertically but when the number of columns is NOT known in advance.
March 7, 2002 at 12:45 pm
Ok I posted an old code template I created to do exactly this, you will need to change some items to match with what they and it is building dynamically the query you want. Currently it also has an 8000 character limit to do the dynamic build and I may relook at this in the near future. So here is the code and let me know if you need help with anything.
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'
/* 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.*/
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply