Join in two tables - 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

Join in two tables

Author

Topic

yaman909
Starting Member

2 Posts

Posted - 2013-10-14 : 11:56:05

I have two tables:
T1 and T2.
T1 has 1 row as
name and age

T2 has 2 rows as
name, money1
name, money2

I use left outer join and get two rows in the resulting table.
As name, age, money1
and name, age, money2

Is there any way using which i can get data like:

name, age, money1, age, money2
in one row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 12:55:01

If you always have only maximum of two money


SELECT t1.name,
t1.age,
MAX(CASE WHEN Seq=1 THEN t2.money END) AS money1,
t1.age,
MAX(CASE WHEN Seq=2 THEN t2.money END) AS money2
FROM T1 t1
INNER JOIN (SELECT name,money,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS Seq
            FROM T2
            GROUP BY name
           )t2
ON t2.name = t1.name
GROUP BY t1.name,t1.age

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

yaman909
Starting Member

2 Posts

Posted - 2013-10-15 : 06:12:15

visakh16,

The rows in the second table can be any number for a given name.
i.e money value can go up to any value for a particular name.
How do I handle that?

divya.ce
Starting Member

16 Posts

Posted - 2013-10-15 : 08:20:49

Please find one solution below, however in this solution at least you should be aware max how many money values will be corresponding to one name..

declare @t1 table(name varchar(100),age varchar(10))
declare @t2 table(name varchar(100),mny int)
insert into @t1
values('a','20')
insert into @t2
values('a',100),
('a',200),
('a',300)

select *
from @t1 t1
left join @t2 t2
on t1.name = t2.name

select *
from
(
select t1.name,t1.age, t2.mny,
newcolname = cast('Money ' as varchar) + cast(ROW_NUMBER() OVER( PARTITION BY t1.name order by mny) as varchar)
from @t1 t1
left join @t2 t2
on t1.name = t2.name
)t
pivot
(min(mny) for newcolname in ([Money 1],[Money 2],[Money 3]))P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:13:07

quote:
Originally posted by yaman909

visakh16,

The rows in the second table can be any number for a given name.
i.e money value can go up to any value for a particular name.
How do I handle that?

So are you looking at dynamically pivotting money values?

If yes see

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources