Technical Article

Pivot Data based on Unknown Field Items

,

I was posed a question a few months back on another site about being about to create a Pivot Table (Horizontal version of vertical data) and I saw the question again today. This will allow you to do that, just make changes to the query where the title is the replacement need. It will build a dynamic query (sorry there is an 8000 character limit) to do this without you having too. You can also specify a time frame. Any question let me know.

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.

Rate

1 (1)

Share

Share

Rate

1 (1)