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
Hi Lawrence,
SQL Server 2008 R2
Using the example below when using the wildcard expansion on SELECT * the results are: This fails when running.
The results should be:
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