temporary table with dinamic SQL

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

SQL Server 2005 Forums

Transact-SQL (2005)

temporary table with dinamic SQL

Author

Topic

lpastor
Starting Member

1 Post

Posted - 2007-08-21 : 15:51:16

Hi I have a problem

my table is similar to

XML_NAME | XML_VALUE
YearOfPublish | 2007
IssueNumber | 28
Autor | John

so I need rezalt that it give me all in one row similar to
YearOfPublish | IssueNumber |Autor
2007 | 28 | John

Yes probably it is 100 times that somebody ask this king of question but I need it to make dynamically ... because the name of columns and it number can be changed in any time ...so I need to go trought all XML_NAME columns rows and link it with XML_VAlUE colum and give me all in one row...I hope I was clear enouf

I start something like this

CREATE TABLE [#headerTemp] -- It is not good by I don't know how to create temporary table dynamically trough some selection
(
[id] [int] IDENTITY ,
[YearOfPublish] [varchar](100),
[IssueNumber] [nvarchar](10),
[Autor] [nvarchar](50)
);

--insert into #headerTemp (id) values(1); //I insert first row in order to update ...but without any sussces

DECLARE HeaderData_Cursor CURSOR FOR
SELECT def.NODE_NAME, m.value FROM EA_MAIL_DATA AS m INNER JOIN
EA_XML_DEF AS def ON m.id_node = def.ID WHERE (id_parent = @id_node) AND (id_mail = @id_mail);//this is select statment that give me all neded data
OPEN HeaderData_Cursor;

set @sqlValues = '';

FETCH NEXT FROM HeaderData_Cursor INTO @columnHeader, @headerValue;
WHILE @@FETCH_STATUS = 0
BEGIN

set @sqlHeader = RTrim(LTrim(Convert(varchar(80),@columnHeader)))-- RTRIM(@columnHeader);-- Convert(nvarchar(50),
set @sqlValue = Replace(@headerValue,' ','_')

set @sqlValues = @sqlValues +''''+ @sqlValue+''''+',';

FETCH NEXT FROM HeaderData_Cursor INTO @columnHeader, @headerValue;
END;
CLOSE HeaderData_Cursor;
DEALLOCATE HeaderData_Cursor;

set @sqlValues = SUBSTRING(@sqlValues, 0, LEN(@sqlValues) )
print @sqlValues

insert into #headerTemp values( Convert(varchar(80),@sqlValues) ) // IT DONT WORK ....

select * from #headerTemp

------------------

So my problem is perhaps that I don't know how to make correct SQL stetment with parameters and this ''' thing because always say that number of columns is not ok ...

HELP ME or give me some idea ....

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-22 : 08:44:50

quote:
my table is similar to

XML_NAME | XML_VALUE
YearOfPublish | 2007
IssueNumber | 28
Autor | John

There is nothing in that table, as posted, that will guarantee the order of the data. Even a cursor will NOT necessarily do what you want because the cursor does not quarantee that the rows will appear in the order that you've posted. That's just the nature of databases.

What makes up the primary key of that table? If there isn't one, then you're toast... no amount of SQL prestidigitation will allow you to resolve this problem without a primary key to group the rows together by article.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources