StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Herewith the solution:</p> <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @internal_fromdate AS SMALLDATETIME, @internal_todate AS SMALLDATETIME; select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') set @internal_fromdate = '2012-04-01 12:15:00'; set @internal_todate = '2012-04-01 12:45:00'; set @query = 'SELECT statdate, ' + @cols + ' from ( select statdate, statcolumnname, statcolumnvalue from [85137_PHY_Long_PG] ) x pivot ( min(statcolumnvalue) for statcolumnname in (' + @cols + ') ) p WHERE statdate BETWEEN @FromDate AND @ToDate ORDER BY statdate' exec sp_executesql @query, N'@FromDate SMALLDATETIME, @ToDate SMALLDATETIME', @FromDate=@internal_fromdate, @ToDate=@internal_todate </code></pre> <p><strong>UPDATE</strong></p> <p>Ok, I have tried the following variations:</p> <pre><code>create proc MyProc9 (@tableName varchar,@dateFrom smalldatetime, @dateTo smalldatetime) AS DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_MP] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') set @query = 'SELECT statdate, ' + @cols + ' from ( SELECT statdate, statcolumnname, statcolumnvalue from @table ) x pivot ( min(statcolumnvalue) for statcolumnname in (' + @cols + ') ) p WHERE statdate BETWEEN @from AND @to ORDER BY statdate' exec sp_executesql @query, N'@table varchar,@from smalldatetime, @to smalldatetime', @table=@tableName,@from=@dateFrom, @to=@dateTo </code></pre> <p>Error: Must declare the table variable "@table".</p> <p>Tried '+@tableName+' in the @query string: Incorrect syntax near '8'.</p> <p>Tried '+QUOTENAME(@tableName)+' in the @query string: Invalid object name '8'.</p> <p>Tried ['+@tableName+'] in the @query string: Invalid object name '8'.</p> <p>Tried QUOTENAME(@table) in the @query string: Invalid object name '8'.</p> <p>Tried [85137_PHY_Long_MP] in the @query string: Works correctly, just want to replace this.</p> <p>Tried [@tableName] in the @query string: Invalid object name '@tableName'.</p> <p>Tried @tableName in the @query string: Must declare the table variable "@tableName".</p> <p>I don't understand how to resolve the problem</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload