Issues with PIVOT

HugoKornelis

Hi!

Not sure if this is still relevant since I am quite behind in updates (runing SQL Prompt 7.1.0.144). But I figured I'd better report what I've seen and let you guys laught it off if this has already been fixed.

When working on a query with PIVOT (admittedly, something I normally avoid when possible), I noticed two things.

Sample query:

USE AdventureWorks2012;
GO

SELECT  Class, [Black], [Blue], [Grey], [Multi], [Red], [Silver], [Silver/Black], [White], [Yellow]
FROM   (SELECT Class, Color, StandardCost
        FROM Production.Product) AS p
PIVOT  (AVG(StandardCost)
        FOR Color IN ([Black], [Blue], [Grey], [Multi], [Red], [Silver], [Silver/Black], [White], [Yellow])) AS pvt;
GO

1. In the final SELECT list, the columns generated by the PIVOT clause are not included in the auto-complete logic. Can be reproduced by copy/pasting the sample query, removing one of the columns in the list and then entering it again.
2.
2. When I reformat the SQL, Prompt incorrectly removes the square brackets, resulting in a syntactical incorrect query as shown below:

SELECT  p.Class,
        Black,
        Blue,
        Grey,
        Multi,
        Red,
        Silver,
        [Silver/Black],
        White,
        Yellow
FROM    (SELECT Class,
                Color,
                StandardCost
         FROM   Production.Product) AS p PIVOT  ( AVG(StandardCost) FOR Color IN (Black,
                                                              Blue, Grey,
                                                              Multi, Red,
                                                              Silver,
                                                              [Silver/Black],
                                                              White,
                                                              Yellow) ) AS pvt;
GO

(Apart from the missing square brackets, the addition of the p. prefix before the Class column name is also incorrect; the PIVOT clause transforms the table, so only pvt.Class is available after, not p.Class. Also, it appears inconsistent that the Class column is prefixed, but the other columns are not)

James R

Hi Hugo, thanks for your post.

From what I can tell on my end the first issue is fixed on the latest version as well as the issue with the alias qualification.

I could use a bit of clarification to help us understand the second issue, as from what I can tell the produced SQL is valid (if you remove the incorrect p.Class column). If you don't want to remove square brackets on format you can turn it off by going to SQL Prompt > Options > Format > Styles > Add / Remove square brackets.

Hope this helps, and I would recommend upgrading to the latest version if you can!
Thanks,
James

HugoKornelis

Hi James,

Thanks for looking into this. I am waiting for a vendor to come back to me with some quotes on replacement laptops so I will not be upgrading any software on this old wreck - but I will definitely jump to the newest versions once I get my new power-beast!

The issue with the reformatted SQL is that the list of colours in the IN list is syntactical incorrect, these are values and need to remain enclosed within [brackets]. Submitting the reformatted code will produce an error message.

EDIT to add: I know that changing the option fixes this, but regardless of the option the tool should never remove brackets that are non-optional.

Best,
Hugo

James R

Hey Hugo,

Is the necessity of those square brackets an option set on your SQL Server instance or something similar? Because I can run that query fine locally against my local database. Forgive me if I'm missing anything obvious!

Regards,
James

HugoKornelis

Huh? That's weird. I was certain that I got an error when I tried this the last time, but now it does run just fine.

The syntax diagram at https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx does seem to suggest that brackets are mandatory, and all the examples (for PIVOT, not for UNPIVOT) have them (but then, they all use numerical values, not strings, which SQLPrompt does indeed leave intact).

I looked a bit further, and the syntax diagrams at https://technet.microsoft.com/en-us/library/ms177634(v=sql.105).aspx show that the IN list is supposed to contain column names, and in that case I have to agree that "Black" and "[Black]" are both correct; this is not a bug after all.

Sorry for wasting your time!

Cheers,
Hugo