SQL Server Combining only few columns to 1 in SQL

oxalkeyp  于 2023-04-28  发布在  其他
关注(0)|答案(4)|浏览(159)

I have 2 tables tbl1, tbl2.

Sample data of tbl1 :
| Id | Name | CreatedOn | SpentAmt |
| ------------ | ------------ | ------------ | ------------ |
| 1 | abc | 2023/03/31 | 1000 |
| 2 | Test | 2023/03/31 | 14000 |
| 3 | Mark | 2023/03/31 | 2000 |
| 4,5 | Robert,Gustin | 2023/03/31 | 700 |

Sample data of tbl2 :

IdNameCreatedOnUsedAmt
1abc2023/03/312000
7Grace2023/03/314000
2Test2023/03/3110000
9,1Mary,abc2023/03/311000

Expected output:

IdNameSpentAmtSpentAmt
1abc10002000
2Test1400010000
3mark2000null
4,5Robert,gustin700null
7gracenull4000
9,1Mary,abcnull1000

This is what I have so far:

SELECT id, name, spentamt AS amt
FROM tbl1
WHERE createdon >= '2021-04-01'
GROUP BY id, name

SELECT id, name, usedamt AS amt
FROM tbl2
WHERE createdon >= '2021-04-01'
GROUP BY id, name

Both individual queries return the expected results, but combining the two isn't working properly

ohfgkhjo

ohfgkhjo1#

This looks like a full join ; I don't think that you need aggregation here.

select coalesce(t1.id, t2.id) as id, 
    coalesce(t1.name, t2.name) as name, 
    t1.spentamt, 
    t2.usedamt
from (select * from tbl1 where createdon >= '2021-04-01') t1
full join (select * from tbl2 where createdon >= '2021-04-01') t2 on t1.id = t2.id

Note that I moved the date filtering within subqueries, so it happens before the full join .

vcudknz3

vcudknz32#

You can use full outer join to combine the two tables like

SELECT COALESCE(tbl1.id_name, tbl2.id_name) AS id_name,
       tbl1.spentamt AS spent_amt,
       tbl2.usedamt AS used_amt
FROM (
    SELECT CONCAT(id, ',', name) AS id_name, spentamt
    FROM tbl1
    WHERE createdon >= '2023/04/01'
) tbl1
FULL OUTER JOIN (
    SELECT CONCAT(id, ',', name) AS id_name, usedamt
    FROM tbl2
    WHERE createdon >= '2023/04/01'
) tbl2 ON tbl1.id_name = tbl2.id_name
9jyewag0

9jyewag03#

You can use Cte or Full join to solve the problem

;with _Listtbl1 as (
                    select id,
                           name,
                           sum( spentamt) as amt
                    FROM tbl1
                    where createdon >= '2021-04-01'
                    group by id,
                           name
),_Listtbl2 as (
            select id,
                   name,
                  sum( usedamt)   as usedamt
            FROM tbl2
            where createdon >= '2021-04-01'
            group by id,
                   name
       )

select ISNULL(a.Id,b.Id) as Id
, ISNULL(a.name,b.name) as name
, a.amt as amt
, b.usedamt as usedamt

from _Listtbl1  a
full join _Listtbl2 b on a.Name=b.Name

order by ISNULL(a.Id,b.Id)

Result

IdIdamtusedamt
1abc10002000
2Test1400010000
3Mark2000NULL
4,5Robert,Gustin700NULL
7GraceNULL4000
9,1Mary,abcNULL1000

--

You can create insert base data with the following statements:

drop table if exists tbl1
create table tbl1(Id nvarchar(500), Name nvarchar(500), CreatedOn date, SpentAmt bigint)

insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('1'   ,'abc', '2023/03/31'    ,1000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('2'   ,'Test',    '2023/03/31'    ,14000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('3'   ,'Mark',    '2023/03/31'    ,2000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('4,5' ,'Robert,Gustin',   '2023/03/31'    ,700)


drop table if exists tbl2
create table tbl2(Id nvarchar(500), Name nvarchar(500), CreatedOn date, UsedAmt bigint)

insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('1'    ,'abc', '2023/03/31'    ,2000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('7'    ,'Grace',   '2023/03/31',   4000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('2'    ,'Test',    '2023/03/31',   10000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('9,1', 'Mary,abc'  ,'2023/03/31',  1000)
5hcedyr0

5hcedyr04#

You can also UNION ALL the tables and then GROUP BY the result:

select id, Name, sum(SpentAmt), sum(UsedAmt)
from
(
  select id, Name, SpentAmt, null as UsedAmt
  from tbl1 WHERE createdon >= '2023/04/01'
  union all
  select id, Name, null, UsedAmt
  from tbl2 WHERE createdon >= '2023/04/01'
) dt
group by id, Name;

相关问题