SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Transform rows to columns.
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

ProgramX
Starting Member

Romania
4 Posts

Posted - 09/20/2006 :  08:29:46  Show Profile
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/20/2006 :  08:44:08  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000