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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Transform rows to columns.

Author  Topic 

ProgramX
Starting Member

4 Posts

Posted - 2006-09-20 : 08:29:46
Hello !

In order to achive this :



USE db

if exists(select * from sysobjects where name = 'proc_proba_coll_row')
begin
drop PROCEDURE proc_proba_coll_row
end
go


CREATE 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)

----------------------c2
DECLARE collumn_cursor CURSOR FOR
SELECT dbo.syscolumns.name
FROM dbo.syscolumns LEFT OUTER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE (dbo.sysobjects.name = 'Centrale')

OPEN collumn_cursor

-- Perform the second fetch.
FETCH NEXT FROM collumn_cursor
INTO @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 = 0

BEGIN
FETCH NEXT FROM collumn_cursor
INTO @var_column
SET @var_table_def = @var_table_def + ',' + @var_column + ' varchar(200)'
END

CLOSE collumn_cursor
DEALLOCATE collumn_cursor
----------------------c2
CREATE TABLE newTableName (@var_table_def)
GO



Server: Msg 170, Level 15, State 1, Procedure proc_proba_coll_row, Line 34
Line 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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page
   

- Advertisement -