sql server data warehouse: Guys I am trying to demoralize the source column in one of my tables so

I can identify the combination of sources that the email was recruited
from.


I know I can do this with a while loop (or cursor) but I would really
prefer taking a
set based approach.

Also, please feel free to offer any ideas on a better way to model
this.


I would like to know if we recruited an email on web site A, web site
B, or web site A and B.


My data table looks something like #data_table and I want my result set

to look something like #hybrid_table.


Can anyone offer some advice on how to approach this problem?


Thanks for you suggestions!!!


--drop table #data_table
create table #data_table (email_id int,src_id int)
--raw data
insert into #data_table select 1,5
insert into #data_table select 1,6
insert into #data_table select 1,7


insert into #data_table select 2,5
insert into #data_table select 2,6
insert into #data_table select 2,7


insert into #data_table select 3,5
insert into #data_table select 3,6
insert into #data_table select 3,7


insert into #data_table select 4,5
insert into #data_table select 4,6


insert into #data_table select 5,5
insert into #data_table select 5,9
insert into #data_table select 5,4
insert into #data_table select 5,20


insert into #data_table select 6,20
insert into #data_table select 6,5
insert into #data_table select 6,9
insert into #data_table select 6,4


--DROP TABLE #hybrid_table
create table #hybrid_table (hybrid_id int identity(1,1),hybrid_name
varchar(50))
insert into #hybrid_table (hybrid_name) SELECT '5,6,7'
insert into #hybrid_table (hybrid_name) SELECT '5,6'
insert into #hybrid_table (hybrid_name) SELECT '4,5,9,20'


select * from #data_table order by email_id,src_id
select * from #hybrid_table

[quoted text, click to view]

Can you explain why denormalizing would help you make that
determination?


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

I guess that is what I am asking too.

I have one email fact. I need to know what site I recruited this email
from. It was recruited from 2 sites. I want to give both credit since
both are good sources for recruiting emails.

How can you model this?

I think it is best to create a hybrid source dimension.


Source Dimension
Category Site Cost Per Email
Partner A Site 1 $1
Partner A Site 2 $10000

Just wanted to bump this.

Does anyone have any suggestions on how to model this scenerio?

How about someing like below?
Source Dimension
Category Site Cost Per Email
Partner A Site 1 $1
Partner A Site 2 $10000
Hybrid Site 1,2 $10001