April 29, 2010 at 9:44 am
I have a table which has id's in string
table 1
ID1 IDS_Field Value1
1 1352,1355,1400 A
2 1252,1356,1971 B
3 243,442 C
Table 2
ID2 Value2
1352 U
1355 V
1400 X
1252 Y
1356 Z
1971 I
243 J
442 K
My Result set should be like this
ID1 ID2 Value2 Value1
1 1352 U A
1 1355 V A
1 1400 X A
2 1252 Y B
2 1356 Z B
2 1971 I B
3 243 J C
3 442 K C
Here is the kind of join I was doing which seems to be not so very optimized as I am trying to use a like operator to implement the join.
select T1.ID1,T2.ID2,T2.Value2,T1.Value1
From Table2 T2 inner join Table1 T1 on ','+T1.IDS+',' LIKE '%,'+CONVERT(T1.ID2)+',%'
Can someone suggest an optimized join for this?
April 29, 2010 at 11:48 am
Yeah, rearchitect the storage so that you've got normalized data.
Barring that, look at this approach for delimited lists from Jeff Moden[/url]. It'll help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2010 at 12:33 pm
First, a disclaimer, I have posted several DelimitedSplit functions here on SSC. If you don't have one, you should search this site for one.
Here is what you are attempting to achieve:
create table dbo.table1(
ID1 int,
IDS_Field varchar(32),
Value1 char(1)
);
insert into dbo.table1
select 1, '1352,1355,1400', 'A' union all
select 2, '1252,1356,1971', 'B' union all
select 3, '243,442', 'C';
create table dbo.table2(
ID2 int,
Value2 char(1)
);
insert into dbo.table2
select 1352, 'U' union all
select 1355, 'V' union all
select 1400, 'X' union all
select 1252, 'Y' union all
select 1356, 'Z' union all
select 1971, 'I' union all
select 243, 'J' union all
select 442, 'K';
with NormalizedData as (
select
t1.ID1,
cast(ds.Item as int) ID2,
t1.Value1
from
dbo.table1 t1
cross apply dbo.DelimitedSplit(t1.IDS_Field, ',') ds
)
select
nd.ID1,
nd.ID2,
t2.Value2,
nd.Value1
from
NormalizedData nd
inner join dbo.table2 t2
on (nd.ID2 = t2.ID2)
order by
nd.ID1,
nd.ID2;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply