Using Rows of one table as Columns of another

[scarmody]

I need help with the following scenario:

I have a table that contains several columns (say 'fld1', 'fld2', 'fld3', 'fld4', 'fld5'). I also have another table which contains 2 columns (one being 'colname' and the other is 'colorder'). The data contained in this last table is as follows:

colname colorder
------- --------
fld1 2
fld2 1
fld3 4
fld4 3

What I want to do is formulate a SELECT statement on the first table using the column names contained as data in the second table. Can this be done?? Any help would be appreciated.

Scott

 

[sunila7]

Hi,

u can try something like this... part of this code is from Terry's post Thread183-109857

Declare @sel varchar(3000)
Declare @str varchar(2000)
Select @str=''
Select top 10 @str=@str+colname +',' from tblcolumns

Select @str=substring(@str,1,datalength(@str)-1)

Set @sel = 'SELECT ' + @Str + ' FROM tbl'
exec(@sel)


Sunil

 

[tlbroadbent]

I'd make some slight modifications to Sunil's code.

Declare @str varchar(2000)
Select @str=''
Select @str=@str+colname + ','
From tblcolumns
--I assume that colorder indicates the order the
--columns should appear in the select list.
Order By colorder

--Remove the final comma
Select @str=substring(@str,1,datalength(@str)-1)

Set @str = 'SELECT ' + @str + ' FROM tbl'
Exec(@str) Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.