How do you use a variable for a column name in the select list? 1

[PennyR]

I don't have the language to phrase this accurately so
I will explain by example.

I have a table with 10 fields:
Table name = tblScore
Column names = id, Score1, Score2,...up to Score9
I have 20 records in the table with scores in each column.

I need to convert the table so that:
rather than having 1 record with 9 scores
I want 9 records with 1 score each (x 20 records)

In the end, I want a list of 180 records
(9 scores x 20 original records)
id, column name, score

The following code is the closest I can come:

--------------------------------------------------------
declare @colname nvarchar(50), @index int

set @index = 2 --start with column with first score
while @index < 10
begin
select @colname = COL_NAME(OBJECT_ID('tblScore'),@index)
from dbo.tblScore


--the following will not treat @colname as a column so I
--do not get the column value returned

select id, COL_NAME(OBJECT_ID('tblScore'),@index) AS ColumnName, @colname AS Score

from dbo.tblScore

set @index = @index + 1
end

---------------------------------------------------------
Please help - I don't how to use a variable in the select list and also don't know what keywords to use to search for faqs.

 

[snitin78]

Hope this will help.

declare @colname nvarchar(50), @index int

set @index = 2 --start with column with first score
while @index < 10
begin
select @colname = COL_NAME(OBJECT_ID('tblScore'),@index)
from dbo.tblScore


--the following will not treat @colname as a column so I
--do not get the column value returned

declare @sql varchar(100)

set @sql = 'select id, '''+@colname+ ''' AS CoumnName, '+@colname+' AS Score from dbo.tblScore'

exec (@sql)

set @index = @index + 1
end

 

[sqlfable]

This would be so trivial and basic if you designed it correctly with a table holding scores only and related it back to whatever identifier it is you use for the entries.

If you are able to alter the schema then you should now before you have more problems down the road.

Also if I read correctly you are trying to PIVOT. So use PIVOT. It's not very clear on the results. You might want to post the results you are trying to accomplish if the explanation isn't coming out right

 

[PennyR]

snitin78

That did it - thank you so much!