Combine two tables horizontally

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

SQL Server 2000 Forums

Transact-SQL (2000)

Combine two tables horizontally

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-13 : 06:29:15

Nilprakash writes "Hello,

I am working on a task to join two different tables into one resultset, but unlike results by UNION<ALL> I want table1 be appearing on left part and table2 on right.

Example:
If I have following data in two tables

Data in Table1
ID DESC AMT
-------------------------
1 TAB1_ROW1 10,000
2 TAB1_ROW2 3,700
3 TAB1_ROW3 7,120

Data in Table2
ID DESC AMT
-------------------------
1 TAB2_ROW1 5,500
2 TAB2_ROW2 2,300

So the required query will return
ID DESC AMT ID DESC AMT
1 TAB1_ROW1 10,000 1 TAB2_ROW1 5,500
2 TAB1_ROW2 3,700 2 TAB2_ROW2 2,300
3 TAB1_ROW3 7,120

Any immediate help is appreciable
Thanks in advance

Nilprakash"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 06:41:21

Select * From Table1 t1 Left outer Join Table2 t2 on t1.ID = t2.ID

Chirag

Nilprakash
Starting Member

3 Posts

Posted - 2006-09-13 : 07:10:16

Hello Chirag,

Thank you for your suggestion/help, now I may proceed with something +ve. Only problem which I see now is, in Table2 IDs are different than those are in Table1, I realised this problem when I ran the suggested query. Can you please look and help me how can I solve this problem if IDs are different, you may give me a different query altogether.

Regards
Nilprakash

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-13 : 07:23:38

NilPrakash, Welcome to SQL Team.!!!

Can you tell me what do you mean IDs are different? Are this 2 tables linked in any way?

The query which i gave you, will only work if this 2 tables are linked, and i found that they are linked to gather with ID field

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-13 : 10:24:40

Where do you want to show data?
Post some more sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Nilprakash
Starting Member

3 Posts

Posted - 2006-09-14 : 00:14:33

ok, I will be more specific to the problem. I have only one table called pasp and the data in it is as below.

tm_id tm_type tm_desc tm_amount
---------------------------------
1 A Basic 9,000
2 A HRA 3,500
3 B EL 210
4 B PF 780
5 A SideAll 500

and I want to split this table in two parts with the help of field tm_type. I was trying following query but which is not working reason-being there is no join. My problem is how can I achieve this with one or couple of SQL statements.

SELECT e.tm_desc AS earn_desc, e.tm_amount AS earn_amt,
d.tm_desc AS ded_desc, d.tm_amount AS ded_amt
FROM (SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'A')) AS e,
(SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'B')) AS d

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 01:18:24

There is a JOIN. A old style CROSS JOIN.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 01:21:50

Since you haven't provided the expected output, all we can do is guessing.

declare @a table (i int identity(0,1), tm_desc varchar(10), tm_amount int)

insert		@a
		(
			tm_desc,
			tm_amount
		)
select		tm_desc,
		tm_amount
from		tran_master
where		tm_type = 'a'
order by	tm_amount desc

declare @b table (i int identity(0,1), tm_desc varchar(10), tm_amount int)

insert		@b
		(
			tm_desc,
			tm_amount
		)
select		tm_desc,
		tm_amount
from		tran_master
where		tm_type = 'b'
order by	tm_amount desc

select		a.tm_desc,
		a.tm_amount,
		b.tm_desc,
		b.tm_amount
from		@a a
full join	@b b on b.i = a.i
order by	a.i,
		b.i

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 01:22:11

Will this help ?


Declare @var Table  
(
   tm_id int, 
   tm_type varchar(10),
   tm_desc varchar(10),
   tm_amount money 
)

Insert @var 
Select 1,'A','Basic',9000 Union All
Select 2, 'A','HRA', 3500 Union All
Select 3, 'B','EL', 210 Union All
Select 4, 'B','PF', 780 Union All
Select 5, 'A','SideAll',500 


Select a.Tm_ID,a.Tm_type,a.tm_desc,a.Tm_amount,b.tm_id,b.tm_type,b.tm_desc,b.tm_amount From 
 (
	Select (Select Count(1) From @var v1 Where tm_type = 'A' and v1.tm_ID <= v.tm_id) As PK, * From @var v
	Where tm_type = 'A'
 ) as A
Full Outer Join 
 (
	Select (Select Count(1) From @var v1 Where tm_type = 'B' and v1.tm_ID <= v.tm_id) As PK, * From @var v
	Where tm_type = 'B'
 ) as B 
on A.pk = b.pk 


(5 row(s) affected)

Tm_ID       Tm_type    tm_desc    Tm_amount             tm_id       tm_type    tm_desc    tm_amount             
----------- ---------- ---------- --------------------- ----------- ---------- ---------- --------------------- 
1           A          Basic      9000.0000             3           B          EL         210.0000
2           A          HRA        3500.0000             4           B          PF         780.0000
5           A          SideAll    500.0000              NULL        NULL       NULL       NULL

(3 row(s) affected)

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 01:23:48

quote:
Originally posted by Peso

There is a JOIN. A old style CROSS JOIN.

Peter Larsson
Helsingborg, Sweden

Peter, What did you mean by this???

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 02:27:51

There is a comma after the first derived table E, and then the next derived table is defined and named D.
So what Nilprakash does, is a CROSS JOIN between all records with tm_type = 'A' (derived table E) and all records with tm_type = 'B' (derived table D).

quote:
Originally posted by Nilprakash

I was trying following query but which is not working reason-being there is no join. My problem is how can I achieve this with one or couple of SQL statements.

SELECT e.tm_desc AS earn_desc, e.tm_amount AS earn_amt,
d.tm_desc AS ded_desc, d.tm_amount AS ded_amt
FROM (SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'A')) AS e,
(SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'B')) AS d

A Cartesian Product, known as CROSS JOIN, was in previous ANSI versions written like above with a comma. Now the practice is to write CROSS JOIN.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 02:36:11

quote:
Originally posted by Peso

There is a comma after the first derived table E, and then the next derived table is defined and named D.
So what Nilprakash does, is a CROSS JOIN between all records with tm_type = 'A' (derived table E) and all records with tm_type = 'B' (derived table D).
quote:
Originally posted by Nilprakash

SELECT e.tm_desc AS earn_desc, e.tm_amount AS earn_amt,
d.tm_desc AS ded_desc, d.tm_amount AS ded_amt
FROM (SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'A')) AS e,
(SELECT tm_desc, tm_amount
FROM tran_master
WHERE (tm_type = 'B')) AS d
A Cartesian Product, known as CROSS JOIN, was in previous ANSI versions written like above with a comma. Now the practice is to write CROSS JOIN.

Peter Larsson
Helsingborg, Sweden

OH..

you were talking about query tried by Nilprakash, i thought ythat this issue can be solved using Cross join..

I misunderstood

Chirag

Nilprakash
Starting Member

3 Posts

Posted - 2006-09-15 : 01:34:42

Thanks a lot to Chirag and Peter for your timely help, both ways it is working.
cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 01:40:48

Thanks for the feedback!
Note that Chirags suggestion sort by ID in both columns and my suggestion sorts by tm_amount.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources