joining the data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

joining the data

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2010-12-14 : 08:55:20

i need a query for the following logic, i have data in 2 tables i need to join them and need to put those data in one table..

table 1

Field1 Field2

A TX
A TX
A VA
B VA
B NJ
C NJ
C NJ
C TN
C TN
D GA
E GA
E TX

TABLE 2

FIELD1 FIELD2 FIELD3
A 110.0 12
B 1312.0 40
C 231.0 34
D 998.0 65
E 23.0 12

I NEED TO PUT TABLE2 DATA IN TABLE1 , I NEED THE OUTPUT LIKE THIS

TABLE3

FIELED1 FIELD2 FIELD3 FIELD4

A TX 110.0 12
A TX 0.0 X
A VA 0.0 X
B VA 1312.0 40
B NJ 0 X
C NJ 231.0 34
C NJ 0.0 X
C TN 0.0 X
C TN 0.0 X
D GA 998.0 65
E GA 23.0 12
E TX 0.0 X

CAN I GET QUERY FOR THIS LOGIC....THANKS

dev

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-14 : 09:11:53

If you are using SQL Server 2005 or higher, you can acheive this using Row_number().

Put the row_number for both the tables and left join the table1 with table2

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2010-12-14 : 09:14:48

i am using 2005 but i am new to sql can i get the query???

dev

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-14 : 09:19:35

SELECT a.Field1,a.StateAbbr
,[field3] = CASE WHEN a.[rank] = 1 THEN a.Field2 ELSE 0 END
,[Field4] = CASE WHEN a.[rank] = 1 THEN a.Field3 ELSE 0 END
FROM

(
select
[rank] = row_number() over(partition by t2.field1 order by t1.field2)
,t2.Field1,t1.Field2 as StateAbbr,t2.field2,t2.Field3
from
@table2 t2
full JOIN @table1 t1 ON t2.field1 = t1.field1
) a

Jim

Everyday I learn something that somebody else already knew

Devart
Posting Yak Master

102 Posts

Posted - 2010-12-14 : 09:30:02

For example:

with _cte
as
(
select
row_number() over(partition by s1.f1 order by s1.f2) as nn,
s1.f1,
s1.f2,
s2.f3,
s2.f4
from
(
select 'A' as f1, 'TX' as f2
union all
select 'A' as f1, 'TX' as f2
union all
select 'A' as f1, 'VA' as f2
union all
select 'B' as f1, 'VA' as f2
union all
select 'B' as f1, 'NJ' as f2
union all
select 'C' as f1, 'NJ' as f2
union all
select 'C' as f1, 'NJ' as f2
union all
select 'C' as f1, 'TN' as f2
union all
select 'C' as f1, 'TN' as f2
union all
select 'D' as f1, 'GA' as f2
union all
select 'E' as f1, 'GA' as f2
union all
select 'E' as f1, 'TX' as f2
) s1
inner join
(
select 'A' as f1, 110.0 as f3, 12 as f4
union all
select 'B' as f1, 1312.0 as f3, 40 as f4
union all
select 'C' as f1, 231.0 as f3, 34 as f4
union all
select 'D' as f1, 998.0 as f3, 65 as f4
union all
select 'E' as f1, 23.0 as f3, 12 as f4
) s2
on s1.f1=s2.f1
)

select
f1,f2,
case when nn=1 then f3 else 0 end as f3,
case when nn=1 then cast(f4 as varchar(10)) else 'X' end as f4
from _cte

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2010-12-15 : 07:32:17

Thank you, it helped me alot..

quote:
Originally posted by jimf

SELECT a.Field1,a.StateAbbr
,[field3] = CASE WHEN a.[rank] = 1 THEN a.Field2 ELSE 0 END
,[Field4] = CASE WHEN a.[rank] = 1 THEN a.Field3 ELSE 0 END
FROM

(
select
[rank] = row_number() over(partition by t2.field1 order by t1.field2)
,t2.Field1,t1.Field2 as StateAbbr,t2.field2,t2.Field3
from
@table2 t2
full JOIN @table1 t1 ON t2.field1 = t1.field1
) a

Jim

Everyday I learn something that somebody else already knew

dev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources