Combining only few columns to 1 in SQL

Question

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:

Id	Name	CreatedOn	UsedAmt
1	abc	2023/03/31	2000
7	Grace	2023/03/31	4000
2	Test	2023/03/31	10000
9,1	Mary,abc	2023/03/31	1000

Expected output:

Id	Name	SpentAmt	SpentAmt
1	abc	1000	2000
2	Test	14000	10000
3	mark	2000	null
4,5	Robert,gustin	700	null
7	grace	null	4000
9,1	Mary,abc	null	1000

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

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Apr 20 '23 at 14:02
*"The data in both id and name columns may not be same in both tables and few of the records might have comma separated values as well"*, just edit your question and provide a minimal reproducible example: ##1-4 — Yitzhak Khabinsky, Apr 20 '23 at 14:15
Given that your data is not normalized (multi-valued attributes illustrated in last row of tbl1) but you are expecting to parse and recontruct normalized records in your query there are 2 problems to solve here. You also did not state how you attempted to "combine" the queries — symcbean, Apr 20 '23 at 14:30
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Apr 20 '23 at 14:47

score 1 · Answer 1 · answered Apr 20 '23 at 14:05

1

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.

answered Apr 20 '23 at 14:05

GMB

216,147
25
84
135

The data in both id and name columns may not be same in both tables and few of the records might have comma separated values as well – Test Apr 20 '23 at 14:11
1

@Test If you have records with comma separated values, that is a **broken** schema and you should do whatever you can to fix it. – Joel Coehoorn Apr 20 '23 at 14:52
its not working its only working for the records which i have only in one table not working for the records which are in both the tables that to with many times i am getting records like duplicates Id Name spentamt usedamt 1 abc NULL 1 abc NULL 1 abc NULL – Test Apr 20 '23 at 14:52
3

@Test Also: running on this on the provided sample data will give the expected result. If this isn't adequate, you need to **update the question** so the sample data covers that scenario. – Joel Coehoorn Apr 20 '23 at 15:03
perhaps you just need to use group by? do you have more than one row with same id per table? – siggemannen Apr 20 '23 at 16:51

score 0 · Answer 2 · answered Apr 20 '23 at 14:58

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

Id	Id	amt	usedamt
1	abc	1000	2000
2	Test	14000	10000
3	Mark	2000	NULL
4,5	Robert,Gustin	700	NULL
7	Grace	NULL	4000
9,1	Mary,abc	NULL	1000

--

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)

score 0 · Answer 3 · answered Apr 20 '23 at 15:12

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;

Combining only few columns to 1 in SQL

3 Answers3