Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ProgramX
Starting Member
4 Posts |
Posted - 2006-09-20 : 08:29:46
|
Hello !In order to achive this :USE dbif exists(select * from sysobjects where name = 'proc_proba_coll_row')begin drop PROCEDURE proc_proba_coll_rowendgoCREATE PROCEDURE proc_proba_coll_row() AS -- Declare the variables to store the values returned by FETCH.DECLARE @var_column varchar(200),@var_table_def varchar(7000)----------------------c2DECLARE collumn_cursor CURSOR FORSELECT dbo.syscolumns.nameFROM dbo.syscolumns LEFT OUTER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.idWHERE (dbo.sysobjects.name = 'Centrale')OPEN collumn_cursor-- Perform the second fetch.FETCH NEXT FROM collumn_cursorINTO @var_column SET @var_table_def = @var_column + ' varchar(200)'-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM collumn_cursor INTO @var_columnSET @var_table_def = @var_table_def + ',' + @var_column + ' varchar(200)'ENDCLOSE collumn_cursorDEALLOCATE collumn_cursor----------------------c2CREATE TABLE newTableName (@var_table_def)GOServer: Msg 170, Level 15, State 1, Procedure proc_proba_coll_row, Line 34Line 34: Incorrect syntax near '@var_table_def'.Please could you hlp me to debug this |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-20 : 08:44:08
|
If you just want to transform rows into column to show in Cross-Tab report, there is an alternate way. Search this forum for "Cross-Tab"Else you will have to use Dynamic sql for your case:Exec('CREATE TABLE newTableName (' + @var_table_def + ')')Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
|
|