Pivot query wildcard expansion gives wrong table alias.

lvbarneslvbarnes Posts: 2
edited September 14, 2015 5:38AM in SQL Prompt
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

Comments

  • 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.
Sign In or Register to comment.