CODEWITHSUNDEEP
sqlsql-server-2008
Ghozian pribadi
Ghozian pribadi

Reputation: 23

SQL- How to join 2 table with same columns and stack it horizontally

I have 2 tables for example:

1st Table:

November

Account    |    Name     |   Segment    |   Collectibility  |  Loan Amount   | AFDA
1             Billy        Corporate           1                   5000         200
2             Randy        Corporate           2                   8000         1000
3             Stan         Commercial          3                   2000         200

2nd Table

December

Account    |    Name     |   Segment    |   Collectibility  |  Loan Amount   | AFDA
1             Billy        Corporate           2                   2000         100
2             Randy        Corporate           3                   4000         500
4             Kenny         Small              1                   2000         100

I'm trying to join the table so the output will be like this

Nov_Dec

Account    |    Name     |   Segment_Nov    |   Segment_Dec     |   Collectibility_Nov  |   Collectibility_Dec  |  Loan Amount_Nov  |   Loan Amount_Dec   | AFDA_Nov    | AFDA_DEC
1               Billy           Corporate       Corporate               1                       2                   5000                    2000            200             100
2               Randy           Corporate       Corporate               2                       3                   8000                    4000            1000            500
3               Stan            Commercial      NULL                    3                       NULL                2000                    NULL            200             NULL
4               Kenny           NULL            Small                   NULL                    1                   NULL                    2000            NULL            100

Usually, I'm taking the long step to achieve that

  1. Union the 'ACCOUNT' column
  2. Modify the design, add new columns
  3. Join the table based on 'ACCOUNT' column that has been combined without any duplicates

Is there any workaround like using subquery to fasten the process? Thank you

Upvotes: 2

Views: 75

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can first UNION the results from different tables, then perform conditional aggregation to pivot the data of each month:

SELECT
    Account,
    Name,
    MAX(CASE WHEN m = 'Nov' THEN Segment END) AS Segment_Nov,
    MAX(CASE WHEN m = 'Dec' THEN Segment END) AS Segment_Dec,
    MAX(CASE WHEN m = 'Dec' THEN Collectibility END) AS Collectibility_Nov,
    MAX(CASE WHEN m = 'Dec' THEN Collectibility END) AS Collectibility_Dec,
    MAX(CASE WHEN m = 'Dec' THEN LoanAmount END) AS LoanAmount_Nov,
    MAX(CASE WHEN m = 'Dec' THEN LoanAmount END) AS LoanAmount_Dec,
    MAX(CASE WHEN m = 'Dec' THEN AFDA END) AS AFDA_Nov,
    MAX(CASE WHEN m = 'Dec' THEN AFDA END) AS AFDA_Dec
FROM
(
    SELECT Account, Name, Segment, Collectibility, LoanAmount, AFDA, 'Nov' AS m
    FROM Table1

    UNION ALL

    SELECT Account, Name, Segment, Collectibility, LoanAmount, AFDA, 'Dec' AS m
    FROM Table2
) AS t
GROUP BY
    Account,
    Name;

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Following this way, you'll get the result:

    DECLARE @Table1 TABLE(Account INT,Name VARCHAR(10),Segment VARCHAR(10),Collectibility INT,LoanAmount INT,AFDA INT)
    INSERT INTO @Table1 VALUES(1,'Billy','Corporate',1,5000,200)
    INSERT INTO @Table1 VALUES(2,'Randy','Corporate',2,8000,1000)
    INSERT INTO @Table1 VALUES(3,'Stan','Commercial',3,2000,200)

    DECLARE @Table2 TABLE(Account INT,Name VARCHAR(10),Segment VARCHAR(10),Collectibility INT,LoanAmount INT,AFDA INT)
    INSERT INTO @Table2 VALUES(1,'Billy','Corporate',2,2000,100)
    INSERT INTO @Table2 VALUES(2,'Randy','Corporate',3,4000,500)
    INSERT INTO @Table2 VALUES(4,'Kenny','Small',1,2000,100)

    SELECT COALESCE(T1.Account,T2.Account)Account,COALESCE(T1.Name,T2.Name)Name
        ,MAX(T1.Segment)Segment_Nov,MAX(T2.Segment)Segment_Dec
        ,MAX(T1.Collectibility)Collectibility_Nov,MAX(T2.Collectibility)Collectibility_Dec
        ,MAX(T1.LoanAmount)LoanAmount_Nov,MAX(T2.LoanAmount)LoanAmount_Dec
        ,MAX(T1.AFDA)AFDA_Nov,MAX(T2.AFDA)AFDA_Dec
    FROM @Table1 T1
    FULL JOIN @Table2 T2 ON T1.Account=T2.Account
    GROUP BY COALESCE(T1.Account,T2.Account),COALESCE(T1.Name,T2.Name)
    ORDER BY COALESCE(T1.Account,T2.Account)

Result:

Account     Name       Segment_Nov Segment_Dec Collectibility_Nov Collectibility_Dec LoanAmount_Nov LoanAmount_Dec AFDA_Nov    AFDA_Dec
----------- ---------- ----------- ----------- ------------------ ------------------ -------------- -------------- ----------- -----------
1           Billy      Corporate   Corporate   1                  2                  5000           2000           200         100
2           Randy      Corporate   Corporate   2                  3                  8000           4000           1000        500
3           Stan       Commercial  NULL        3                  NULL               2000           NULL           200         NULL
4           Kenny      NULL        Small       NULL               1                  NULL           2000           NULL        100

Upvotes: 0

Related Questions