New features added to SQL Prompt Watch now.

Error in star expansion in SQL prompt?

I think I found an error in SQL prompt column expansion. It seems to not be able to parse multiple PIVOT statements correctly.
Consider this statement:
WITH q AS (SELECT 
           'function_' + CAST(seq AS VARCHAR(1)) AS lvl_1,
           'arg_' + CAST(seq AS VARCHAR(1)) AS lvl_2,
           function_name,
           arguments
    FROM (VALUES (1, 'a', 1), (2, 'b', 2)) b(seq, function_name, arguments)
)
SELECT *
FROM q AS re
    PIVOT
    (
        MAX(function_name)
        FOR lvl_1 IN (function_0, function_1, function_2, function_3, function_4, function_5)
    ) b
    PIVOT
    (
        MAX(arguments)
        FOR lvl_2 IN (arg_0, arg_1, arg_2, arg_3, arg_4, arg_5)
    ) c
If you run it, it creates columns function_0 to function_5 and arg_0 to arg_5. But if you place the cursor behind the asterisk in the SELECT line and let SQL prompt expand the column names, only the last PIVOT statement is considered, the expansion still inserts lvl_1 and function_name as arguments.
Tagged:

Answers

Sign In or Register to comment.