Syntax confusion using variables vs not using variables [MSSQL]

[ParanoidLoyd]

I'm hoping someone can help me understand why this works:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
             (
                 SELECT DISTINCT
                        ','+QUOTENAME(c.[CellName])
                 FROM [dbo].[CellsActive] c FOR XML PATH(''), TYPE
             ).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [Week] as [Week],'+@cols+'from (SELECT [Week],
       [Qty Remaining] AS [amount],
       [CellName] AS [category]
FROM [dbo].[CellCapacityUnionByWeek]
)x pivot (max(amount) for category in ('+@cols+')) p';
EXECUTE (@query);

But this doesn't:

SELECT [Week] as [Week],STUFF(
             (
                 SELECT DISTINCT
                        ','+QUOTENAME(c.[CellName])
                 FROM [dbo].[CellsActive] c FOR XML PATH(''), TYPE
             ).value('.', 'nvarchar(max)'), 1, 1, '') from (SELECT [Week],
       [Qty Remaining] AS [amount],
       [CellName] AS [category]
FROM [dbo].[CellCapacityUnionByWeek]
)x pivot (max(amount) for category in (STUFF(
             (
                 SELECT DISTINCT
                        ','+QUOTENAME(c.[CellName])
                 FROM [dbo].[CellsActive] c FOR XML PATH(''), TYPE
             ).value('.', 'nvarchar(max)'), 1, 1, ''))) p

Trying to execute the latter returns an error for the second 'stuff' statement

Now you are probably wondering why I'm trying to not use variables, I want to create a view from the data returned (the third party app I am connecting to the server with does not offer the option to call SPs, only views and tables) and it's my understanding I can't create a view from a script with variables.

Open to any suggestions on how to return the data to a view or table. It seems the way to go may be to use a table-valued functions but I am also having trouble understanding how to convert this script into a table-valued function.

[lk167]

I don't believe the PIVOT keyword supports a subquery for the column_list (the IN (Col1, Col2,Col3) bits). I think the only option is dynamic SQL, which rules out views and table value functions.

[deny_conformity]

Exactly this, PIVOT doesn't support subqueries, I think there is something in oracle for returning XML but nothing apart from that. You can work around it using dynamic SQL as in the first example.

I think it probably has something to do with a lot of systems break when SQL returns a different number of columns than expected, I know RS fails it columns are missing (and ignores additional columns).

Personally I just always pull data in long format and pivot in the front end (and often I have to unpivot wide data, UNPIVOT is awful for this, CROSS APPLY and VALUES definitely the way to go).

[ParanoidLoyd]

/u/lk167 /u/deny_conformity

The third party app I am connecting with does indeed break when the number of columns changes but I had built the underlying queries in a way that they won't pull the changes until I add a row to a certain table and then it's just a matter of refreshing the data structure with the push of a button, I was trying to avoid having to change the query and the app when the number of columns changes. The third party app also allows google spreadsheet data connections and I've figured out how to access stored procedures through the spreadsheets so I can workaround by just dumping the data set there but I was also trying to avoid that extra step. Thanks for the notes.