Dynamically create table from other tables.

[M8KWR]

Hi,

Unsure how to explain what I am trying to achieve, so please bare with me.

I have a table containing field information that are against certain internal campaigns. We use this to display the fields on our internal crm.

What I am now trying to do is use this information within this table, to obtain the data from the table that it is referencing.

This is how the information is stored with out Fields table

Campaign_id Contact_ID Table Field_Name
1 C1 Campaign Field1
1 C2 Campaign Field2
1 C3 Campaign Field3
2 C1 Campaign Field1
2 C2 Campaign Field2
3 C1 Campaign Field1



Then the campaign table will have the Field1-3 columns, along with a Campaign_ID and Contact_ID column, storing the data accordingly.

What I am looking to do is give something the Campaign_ID and Contact_ID and then for it to know what data I need displaying back from the fields. So for example if I gave it Campaign_ID = 1 and Contact_ID = C1 then I would get

Campaign_ID Contact_ID Field1 Field2 Field3
1 C1 Value1 Value2 Value3

Any help or assistance would be amazing, or be able point me in the correct direction to get this to work

Many thanks for any help in advance

 

[simian336]

I think I am with you, if you gave it Campaign_ID = 1 and Contact_ID = C3 you would expect

Campaign_ID Contact_ID Field1 Field2 Field3
1 C1 Value3 Value4 Value5

is that correct?

Simi

 

[M8KWR]

Sorry, I thought I had re-read my questions...

In the fields table there is no Contact_ID column.

Just the field information for a given campaign.

And then in the campaign information it holds the campaign answer for x number of contacts.

So the campaign_id would get the fields that are used for that given campaign, and then the contact_id would be able to query the campaign table for the given contact.

Hope that kinds of explains it.

 

[simian336]

Perhaps anouther example might help.

Simi

 

[M8KWR]

Sorry, will try again...

Fields table.

Campaign_ID Table FieldName
1 Campaign Field1
1 Campaign Field2
1 Campaign Field3
2 Campaign Field1
2 Campaign Field2
3 Campaign Field3


The data is stored in the campaign table. Column Layout

Contact_ID Campaign_ID Field1 Field2 Field3 etc
A 1 Value1 Value2 Value3
B 1 Value6 Value8 Value3
C 1 Value1 Value2 Value3
A 2 Value1 Value2
B 2 Value1 Value2
C 3 Value13

Basically all i am looking at doing is to give something a campaign_id and contact_id.

This will obtain the correct fields from the fields table, and then get the data from the campaigns table.

So if I sent Campaign_id = 1 and contact_id = B then i would get

Column1 Column2
Field1 Value6
Field2 Value8

But if i sent campaign_id = 3 and contact_id = C then i would get

Column1 Column2
Field3 Value13


Hope that is more clear. Just trying to dynamically get the data is a usable format for crystal reports.

 

[simian336]

This is rough but you can play with it...

create table #temp (Campaign_ID integer,

varchar(10)
, FieldName varchar(10))

insert into #temp values (1, 'Campaign','Field1')
insert into #temp values (1, 'Campaign','Field2')
insert into #temp values (1, 'Campaign','Field3')
insert into #temp values (2, 'Campaign','Field1')
insert into #temp values (2, 'Campaign','Field2')
insert into #temp values (3, 'Campaign','Field3')

select * from #temp

DECLARE @fields as varchar(200)
declare @sqlstr varchar(500)
declare @Campaign as int
declare @contact as char(1)

set @Campaign =1
set @contact= 'B'

set @fields=''
SELECT @fields = ISNULL(@fields+', ','')+fieldname
FROM #temp
WHERE campaign_id = @Campaign

SELECT @fields = substring(@fields, 3,200)

print @fields

set @sqlstr= 'select ' + @fields + ' from campaign where Contact_ID = ' +
char(39) + @contact + char(39)

print @sqlstr

--exec @sqlstr

simi