Auto complete/intellisense suggestions using PIVOT
Using PIVOT the wrong alias is suggested in the intellisense window as it is the alias from the FROM clause, not the PIVOT clause that is displayed.
The same goes for the auto complete, where field names is (automatically) prefixed with the wrong alias (I know this feature can be disabled...)
Ex:[code]CREATE TABLE dbo.tbTest(
INSERT INTO dbo.tbTest
VALUES (1, 'foo', 2)
, (2, 'bar', 3)
, (3, 'foo', 4)
FROM dbo.tbTest AS tt
MAX(bar) FOR foo IN ([foo], [bar])
Expanding the * gives the pvt alias, not the p alias. The same goes for the intellisense suggestions and automated prefix additions...
This is included in SQL Prompt 7.1 which you can download from http://www.red-gate.com/products/sql-development/sql-prompt/
Lawrence Barnes commented
When using SQL Prompt formatting for large scripts I have to go back and fix any pivots/unpivots. Missing some of them has bit me a few times. This will be a huge win when fixed!
Yep. intellisense is way off in pivots. It doesn't appear to know anything about them.