Sql merge multiple rows into column

Question

I have 2 tables

Table A

A1 | A2
 1 | 2
 2 | 3
 3 | 4

Table B

B1 | B2
1  | 3
1  | 5
4  | 3

A1,A2,B1 and B2 are all ID's

I want to join Table A with Table B only when A.A1 = B.B1.

Select A.A1, A.A2, B.B2 from A JOIN B ON A.A1 = B.B1

should return

A1 | A2 | B2
1  | 2  | 3
1  | 2  | 5

But i want obtain data in this format, i would like final result as:

A1   | Col2
1    | 2
1    | 3
1    | 5

Extra question: How can i know from which column information comes?

A1   | Col2 | Table
1    | 2    | A
1    | 3    | B
1    | 5    | B

Thx for the help.

Edit1: Union wont work, i dont want to stack simply the fields from both tables, i want join data under a condition, but since A2 and B2 are ID´s of same type i would like to have data in a single collumn, and it would simplify future queries over the result.

Please clarify WHY you want this, and please go back and accept some answers to your past questions. — JNK
– JNK, Commented Aug 24, 2011 at 15:31
i didnt forgot them, need really to back at them, vote in the one more near and tell my solution — blueomega
– blueomega, Commented Aug 24, 2011 at 18:54

Allan · Accepted Answer · 2011-08-26 17:21:19Z

2

To present multiple tables as a single table, you use UNION:

SELECT A1 as Col1, A2 as Col2, 'A' as Col3 FROM table_A
UNION ALL
SELECT B1 as Col1, B2 as Col2, 'B' as Col3 FROM table_B

Based on the revised question, the addition of a where condition provides the result you're looking for. I still don't see any reason that you need a join, based on the scenario presented.

SELECT * FROM
   (SELECT A1, A2 as Col2, 'A' as "TABLE" FROM table_A
    UNION ALL
    SELECT B1, B2 as Col2, 'B' as "TABLE" FROM table_B)
WHERE A1 = 1;

edited Aug 26, 2011 at 17:21

answered Aug 24, 2011 at 15:28

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

JNK Over a year ago

Not what he's after I don't think.

Allan Over a year ago

@JNK: He wants 4 columns from table A and 4 columns from table B to appear as 8 columns in the result set, ideally while knowing which table each row originated in. I don't see anything in the question to lead me to believe that this is not the solution the OP is looking for.

JNK Over a year ago

are you sure you are looking at the right question? He wants two fields from A and two fields from B merged into a single result set with 2 fields, but normalized from 3 fields.

Allan Over a year ago

@JNK: Look at the data the OP is presenting, not the descriptive text. The OP clearly is unfamiliar with the correct terminology and process, but the result they're looking for is definitely the result of a union.

Andriy M Over a year ago

@Allan: I agree with your solution, but you should not say columns when you are talking about rows, that is very confusing.

|

Marius Burz · Accepted Answer · 2011-08-24 19:34:17Z

0

Not very clear your question is, but what you want this might be it.

CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);

INSERT INTO a VALUES(1, 2);
INSERT INTO a VALUES(2, 3);
INSERT INTO a VALUES(3, 4);

INSERT INTO b VALUES(1, 3);
INSERT INTO b VALUES(1, 5);
INSERT INTO b VALUES(4, 3);

COMMIT;

SELECT a1 AS "1", a2 AS "2", 'A' AS "src" FROM a
UNION
SELECT a1, b2, 'B' FROM a, b WHERE a1 = b1;

1                      2                      src 
---------------------- ---------------------- --- 
1                      2                      A   
1                      3                      B   
1                      5                      B   
2                      3                      A   
3                      4                      A

edited Aug 24, 2011 at 19:34

answered Aug 24, 2011 at 18:56

Marius Burz

4,6452 gold badges20 silver badges28 bronze badges

Comments

Andriy M · Accepted Answer · 2011-08-24 20:07:47Z

0

Something like this, possibly:

SELECT
  u.*
FROM (
  SELECT A1, B1 AS Col2, 'A' AS SourceTable FROM A
  UNION ALL
  SELECT B1, B2 AS Col2, 'B' AS SourceTable FROM B
) u
  INNER JOIN (
    SELECT A1 FROM A
    INTERSECT
    SELECT B1 FROM B
  ) i ON u.A1 = i.A1

edited Aug 24, 2011 at 20:07

answered Aug 24, 2011 at 19:29

Andriy M

78.1k18 gold badges100 silver badges157 bronze badges

2 Comments

Marius Burz Over a year ago

I think you need to remove the two AS in AS u and AS i otherwise it won't run.

Andriy M Over a year ago

Done. Knowing little about Oracle, I had to look up if it supported INTERSECT, but I couldn't imagine that AS (with table aliases) would not be supported. Thanks!

Collectives™ on Stack Overflow

Sql merge multiple rows into column

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related