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]')
