Column IDs

[MissTipps]

Hope this is easy (I just can't figure it in T-SQL)

I need to transform some data, moving iteratively through the columns of one table, to create a row for every value in a new table. The old table has column headings being months of the year. So I need to transform each record for that month into a record in the new table. I'm hoping to use some kind of column ordinal property to iterate through the base table in the style of:

set @month = 1

WHILE @month <13
BEGIN

INSERT INTO new_table(Item_Name, Item_Value)
SELECT Old_item_Name, column(@month) FROM Old_Table
set @month = @month + 1
END

Its the column(@month) bit which I can't sort out. I know other DBMSs allow you to reference columns in this way, and I refuse to be beaten on this one.

Many thanks

 

[glennfishwick]

A query such as:

select *
from sysobjects o, syscolumns c
where o.name = 'patients'
and o.id = c.id

Will return all the columns in a particular table. The colid column gives you a sequential ordering through the columns.

 

[MissTipps]

I understand how the ordinal values work, what I need to know is, how do I reference it in my example (see 1st posting)

 

[sunila7]

Hi,

Try this....

declare @SQL varchar(2000)

set @month = 1

WHILE @month <13
BEGIN

set @SQL = 'INSERT INTO new_table(Item_Name, Item_Value)
SELECT Old_item_Name, column' + convert(varchar(2),@month) + ' FROM Old_Table'

exec(@SQL)

set @month = @month + 1
END

Sunil

 

[tlbroadbent]

You can create and execute a dynamic SQL statement as follows.

Declare
@month tinyint,
@colname varchar(40),
@sql varchar(400)
Set @month = 1

WHILE @month <13
BEGIN
Select @colname=name
From syscolumns
--Adjust @month if the first month
--isn't the 1st column in the table
Where id=object_id('table_name')
And colid=@month

Select @sql=
'INSERT INTO new_table(Item_Name, Item_Value)' +
' SELECT Old_item_Name, ' + @colname +
' FROM Old_Table'

Exec(@sql)

Select @month = @month + 1

END
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[MissTipps]

Cheers guys