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(
Id INT,
foo VARCHAR(3),
bar FLOAT
)
GO
INSERT INTO dbo.tbTest
VALUES (1, 'foo', 2)
, (2, 'bar', 3)
, (3, 'foo', 4)
GO
SELECT *
FROM (
SELECT *
FROM dbo.tbTest AS tt
) pvt
PIVOT (
MAX(bar) FOR foo IN ([foo], [bar])
)p
[/code]
Expanding the * gives the pvt alias, not the p alias. The same goes for the intellisense suggestions and automated prefix additions...
Mads Fog
shared this idea
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!
-
James
commented
Yep. intellisense is way off in pivots. It doesn't appear to know anything about them.