Guys
I have a data table that contains emails that could come from one or
more source. I want to add a column to the table to reflect the
combination of sources that the record came from.
Basically I am trying to create the hybird_src and the hybrid tables
from the data_table. Then I want to update a new column on data table
with hybrid_id.
I know I can do this with a cursor, but it seems like there should be a
better (set based) way to accomplish this. Does anyone have any
suggestions?
create table #data_table(email_id int,src_id int)
--raw data
insert into #data_table (1,5)
insert into #data_table (1,6)
insert into #data_table (1,7)
insert into #data_table (2,5)
insert into #data_table (2,6)
insert into #data_table (2,7)
insert into #data_table (3,5)
insert into #data_table (3,6)
insert into #data_table (3,7)
insert into #data_table (4,5)
insert into #data_table (4,6)
insert into #data_table (5,5)
insert into #data_table (5,9)
insert into #data_table (5,4)
insert into #data_table (5,20)
insert into #data_table (6,20)
insert into #data_table (6,5)
insert into #data_table (6,9)
insert into #data_table (6,4)
create table #hybrid_src (hybrid_id int,src_id int)
--results I am looking for
insert into #hybrid_src (1,5)
insert into #hybrid_src (1,6)
insert into #hybrid_src (1,7)
insert into #hybrid_src (2,5)
insert into #hybrid_src (2,6)
insert into #hybrid_src (3,5)
insert into #hybrid_src (3,9)
insert into #hybrid_src (3,4)
insert into #hybrid_src (3,20)
create table #hybrid(hybrid_id int,hybrid_name varchar(200))
--results I am looking for
INSERT INTO #hybrid(1,'5,6,7')
INSERT INTO #hybrid(2,'5,6')
INSERT INTO #hybrid(3,'4,5,9,20')Dave wrote:
> Guys
> I have a data table that contains emails that could come from one or
> more source. I want to add a column to the table to reflect the
> combination of sources that the record came from.
> Basically I am trying to create the hybird_src and the hybrid tables
> from the data_table. Then I want to update a new column on data table
> with hybrid_id.
> I know I can do this with a cursor, but it seems like there should be a
> better (set based) way to accomplish this. Does anyone have any
> suggestions?
>
Why would you want to destroy the apparently sensible and practical
design you already have by kludging it into the "hybrid" tables that
you say you want? Concatenating lots of values together in a column
just results in redundancy and denormalization. If you need to display
it that way in a report then do it in your presentation tier, not in
the database.
David Portas
SQL Server MVP
--|||I am trying to accurately reflect which source an email came from.
Since an email can belong to more than one source it makes since (at
least to me so far) to report on a hybrid of all the valid sources.
If I model email transactions in a Datamart the fact grain would be the
email. I need a source dimension.
Any feedback on this would be very helpful. Including a better way to
model this.
