May 12, 2015 at 1:34 am
hi to all,
I just wonder how to to solve this...
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source)
when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
anyone ??
Regards,
Guus Kramer
----------------------------------------------------------------------------------------
select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], , [V] )) as value
select * from #temp order by .........
versus
select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
select * from #temp order by .....
----------------------------------------------------------------------------------------
May 12, 2015 at 1:55 am
"The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur."
Simple solution: Normalize.
If your table structure is continually changing, it's a dead giveaway that your structure is wrong.
May 12, 2015 at 2:20 am
Ten Centuries,
It is not the structure of the table what is changing but the values the PIVOT needs to create based upon values in the #source table.
I Use this structure to report on all SQL servers for the size of all volumes (standard and luns connected on mountpoints).
For the most servers this is standard but some servers are different and not all servers are questioned right now.
As soon as I add some more servers - which have a different volume structure (which is not manual setup in the PIVOT column's IN section) it wil not show up..... and I have to manualy alter the script ( again and again and again ....)
This what i want to prevent from happening.
Guus
May 12, 2015 at 2:31 am
A place I used to work at had user-defined tables within the database, so users could expand the reporting beyond what was supplied as a default. So I've encountered the same issue myself.
I don't have the code anymore but it basically involved extracting the column names into a table, building a variable with those comma-seperated names and then running a dynamic sql statement:
DECLARE @SQL AS VARCHAR(500), @Stuff AS VARCHAR(500)
CREATE TABLE #TestTable(
Column1INT,
Column2INT);
SELECT @Stuff = STUFF((SELECT ', ' + column_name
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like '#testtable%'
ORDER BY column_name
FOR XML PATH('')), 1, 2, '');
SET @SQL = 'select *
into #temp
from #source
pivot ( avg(value) for drive in (' + @Stuff + ' )) as value'
SELECT @SQL;
DROP TABLE #TestTable
Because you have a temp table it may be a little more involved when identifying the correct temp table, because there may be several of these tables in tempdb at the same time, if several copies of this proc are executing. I think I dynamically generated a table name, so it was always unique within each run of the proc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply