Pivot query wildcard expansion gives wrong table alias.

lvbarnes

SQL Prompt Version 7.0.0.52
SQL Server 2008 R2

Using the example below when using the wildcard expansion on SELECT * the results are:

SELECT data.Color , data.Shape , data.Quantity.

This fails when running.

The results should be:

SELECT pvt.Shape, pvt.Blue, pvt.Red, pvt.Green

Net effect is that I have to go through a procedure and correct pivot (and unpivot) functions after applying [Format SQL]

A fix (or having pivot/unpivot functions ignored) would be appreciated!

Lawrence Barnes

CREATE TABLE #tmp (Color VARCHAR(10), Shape VARCHAR(10), Quantity INT)
INSERT INTO #tmp ( Color, Shape, Quantity )
SELECT x.Color, y.Shape, z.Quantity
FROM (VALUES('Blue'),('Red'),('Green')) x(Color)
CROSS JOIN (VALUES('Square'),('Rectangle'),('Triangle'),('Rhombus')) y(Shape)
CROSS JOIN (VALUES(1),(2),(3),(4),(5)) z(Quantity)
SELECT *
FROM (SELECT t.Color, t.Shape, t.Quantity FROM #tmp t) data
PIVOT(SUM(Quantity) FOR Color IN(Blue, Red, Green)) pvt
ORDER BY Shape DESC
DROP TABLE #tmp

Aaron L

Hi Lawrence,

Thanks for your post - we're aware the suggestions are incorrect if a PIVOT clause is involved and we're hoping to improve them in the next version of SQL Prompt.

We've got a UserVoice request logged for it here and if you add a vote against the idea it'll notify you when we have a new build ready.

Thanks,
Aaron.