[RESOLVED] updating dynamically generated columns

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)

[RESOLVED] updating dynamically generated columns

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-17 : 17:58:43

I am creating a pivot table with dynamically generated columns and I need to perform and update on these columns depending on the value they contain. How do I determine the column names and set up my update statement?
Any guidance would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-17 : 20:00:26

maybe you can post your table schema and your query and with some sample data and show us what do you want to achieve

KH
[spoiler]Time is always against us[/spoiler]

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 10:39:39

my #temp table looks like this:
tmID period1-4 period2-5 period3-9
(char) (char) (char) (char)
-------------------------------------------------
01012 NULL NULL NULL
01013 NULL NULL 2
01014 1 1 2
01015 2 1 2
01016 1 2 1

The columns period... are dynamically generated.
I need a query to update the NULL values to '0'
I know I can retrieve the column names with:

select quotename(name) from tempdb.sys.columns where object_id = object_id('tempdb..#temp')

but how can I construct and execute an update statement?

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 11:16:18

I've got another piece of the puzzle together:


DECLARE @query NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
--construct query as variable to be executed
SELECT @query = 'UPDATE #temp SET ' +
 quotename(name) + ' = 0 WHERE ' + quotename(name) + ' IS NULL ,'
 from tempdb.sys.columns where object_id = object_id('tempdb..#temp') and len(name)like 'period%'
select @SQL = Left(@query,Len(@query)-1) --remove comma character
EXECUTE(@SQL)

This gives me the following statement that I can execute:

UPDATE #temp SET [period1-4] = 0 WHERE [period1-42] IS NULL

This only returns the name for one column however. I would need a way to add all column names from my #temp table to the query construct.
Any ideas?

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-18 : 13:21:18

OK - I figured it out. Not very elegant but here's the solution I used:


declare @sql nvarchar(max)
SELECT @sql= COALESCE(@sql + ' ', '') + 
   'UPDATE #temp SET ' + quotename(name))+ ' = 0 WHERE ' + quotename(name) + ' IS NULL '
FROM tempdb.sys.columns 
WHERE object_id = object_id('tempdb..#temp') and LEN(name)LIKE 'period%'
EXECUTE (@sql)

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-02-18 : 22:52:39

quote:

Originally posted by KlausEngel

OK - I figured it out. Not very elegant but here's the solution I used:


declare @sql nvarchar(max)
SELECT @sql= COALESCE(@sql + ' ', '') + 
   'UPDATE #temp SET ' + quotename(name))+ ' = 0 WHERE ' + quotename(name) + ' IS NULL '
FROM tempdb.sys.columns 
WHERE object_id = object_id('tempdb..#temp') and LEN(name)LIKE 'period%'
EXECUTE (@sql)

I like the way you approached and solved this problem, I tried it out but could not get the updated values.
Would appreciate if you could try it out again and give me the updated values.
Thanks
Glen

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-19 : 23:03:54

quote:

I like the way you approached and solved this problem, I tried it out but could not get the updated values.
Would appreciate if you could try it out again and give me the updated values.
Thanks
Glen


--update NULL values to '0'
DECLARE @sql nvarchar(MAX)
SELECT @sql= COALESCE(@sql + ' ', '') + 
   'UPDATE #temp SET ' + quotename(name)+ ' = 0 WHERE ' + quotename(name) + ' IS NULL '
FROM tempdb.sys.columns  WHERE object_id = object_id('tempdb..#temp') AND LEN(name) = 9
EXECUTE (@sql)

Sorry - I put in the wrong parameter in the WHERE clause. It should be LEN(name)= and then the length of your column name. I hope this helps.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-02-20 : 18:21:26

quote:
Originally posted by KlausEngel

quote:

I like the way you approached and solved this problem, I tried it out but could not get the updated values.
Would appreciate if you could try it out again and give me the updated values.
Thanks
Glen

--update NULL values to '0'
DECLARE @sql nvarchar(MAX)
SELECT @sql= COALESCE(@sql + ' ', '') +
'UPDATE #temp SET ' + quotename(name)+ ' = 0 WHERE ' + quotename(name) + ' IS NULL '
FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#temp') AND LEN(name) = 9
EXECUTE (@sql)

Sorry - I put in the wrong parameter in the WHERE clause. It should be LEN(name)= and then the length of your column name. I hope this helps.

I tried it out again , but still couldnt get the answer
The result was ---

(5 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

The exec(@sql) does not produce the updated result, and even if you use Print @sql to see whether the string is displayed it doesnt show up. The mistake is in the where clause.
Remove the where clause and Print @sql shows the statement.
I am really keen on seeing it through as it is some thing I havent seen for the period I have been with sql.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2011-02-20 : 18:49:58

quote:
Originally posted by glendcruz

quote:
Originally posted by KlausEngel

quote:

I like the way you approached and solved this problem, I tried it out but could not get the updated values.
Would appreciate if you could try it out again and give me the updated values.
Thanks
Glen

--update NULL values to '0'
DECLARE @sql nvarchar(MAX)
SELECT @sql= COALESCE(@sql + ' ', '') +
'UPDATE #temp SET ' + quotename(name)+ ' = 0 WHERE ' + quotename(name) + ' IS NULL '
FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#temp') AND LEN(name) = 9
EXECUTE (@sql)

Sorry - I put in the wrong parameter in the WHERE clause. It should be LEN(name)= and then the length of your column name. I hope this helps.

I tried it out again , but still couldnt get the answer
The result was ---

(5 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

The exec(@sql) does not produce the updated result, and even if you use Print @sql to see whether the string is displayed it doesnt show up. The mistake is in the where clause.
Remove the where clause and Print @sql shows the statement.
I am really keen on seeing it through as it is some thing I havent seen for the period I have been with sql.

Thanks Klaus
I tried it out again. I am sorry I made a mistake and got the desired results. I just altered the where clause to ---where len(name) = len(name)and the print worked too.
I still cannot figure why it worked here as it is the same as what you had done and nothing different.

--result

tmID period1-4 period2-5 period3-9
------ --------- --------- ---------
01012 0 0 0
01013 0 0 2
01014 1 1 2
01015 2 1 2
01016 1 2 1
Thanks a lot and it was great. Nice to know there are gurus out there in this subject.
Keep it up and much appreciated.
Glen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources