Relating the field name use as data in other table

hi!

i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows

FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]

OrigTable

OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok

so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..

my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is

create procedure Definition
@.SearchKey int,
@.FieldTypeint
as
begin
DECLARE @.FieldName varchar(10),
@.FieldDesc varchar(30),
@.TextColor int,
@.mysql varchar(4000)
SET NOCOUNT ON

SELECT @.mysql = 'select o.origkey, '

DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @.ResultType

Open MyCursor
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.mysql = @.mysql + 'o.' + @.FieldName + ' as ' + @.FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @.FieldName, @.FieldDesc
END

CLOSE MyCursor
DEALLOCATE MyCursor

select @.mysql = @.mysql + ' o.results from OrigTable o where
FieldType = ' + @.ResultType
execute(@.mysql)

end

the result is Ok as

OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok

But the problem is how to include the TxtColor or concatenate TxtColor with this output below

OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok

thanx,
mygt

Here it is – You need not to have cursor here.

Code Snippet

Create Table #definitiontable (

[FieldType] int ,

[FieldName1] Varchar(100) ,

[FieldDesc] Varchar(100) ,

[TxtColor] Varchar(100) ,

[FontStyle] Varchar(100)

);

Insert Into #definitiontable Values('8','FIELD1','ABC','#1111','[fsbold]');

Insert Into #definitiontable Values('8','FIELD2','CDE','#2222','[fsitalic]');

Insert Into #definitiontable Values('8','FIELD3','EFG','#3333','[fsbold]');

Create Table #origtable (

[OrigKey] int ,

[FieldType] int ,

[FIELD1] Varchar(100) ,

[FIELD2] Varchar(100) ,

[FIELD3] Varchar(100) ,

[Results] Varchar(100)

);

Insert Into #origtable Values('1','8','test1','test2','test3','ok');

Code Snippet

--For Manual Query

select

[origkey],

max(case when a.[fieldname1] = 'field1' then [field1] + '+' + [txtcolor] end) [ABC],

max(case when a.[fieldname1] = 'field2' then [field2] + '+' + [txtcolor] end) [CDE],

max(case when a.[fieldname1] = 'field3' then [field3] + '+' + [txtcolor] end) [EFG],

[results]

from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]

Code Snippet

--For Dynamic Column Names

Declare @.SQL as varchar(8000);

Declare @.PreparedColumn as Varchar(8000);

Declare @.Columns as Varchar(8000);

Set @.PreparedColumn = ',max(case when a.[fieldname1] = ''?1'' then [?1] + ''+''+ [txtcolor] end) [?2]'

Set @.Columns = '[origkey]'

Select @.Columns = @.Columns + Replace(Replace(@.PreparedColumn,'?1', [FieldName1]),'?2', [FieldDesc])

From #definitiontable

Set @.Columns = @.Columns + ',[results]'

Exec ('Select ' + @.Columns + ' from #definitiontable a

join #origtable b on a.[fieldtype] = b.[fieldtype]

group by

[origkey],

[results]')