SQL Server Adding data from the second table to the first matching record in first table and setting the rest to zero

qgzx9mmu 于 2023-04-28 发布在其他

关注(0)|答案(1)|浏览(146)

I’m trying to join two tables in SQL Server as below: (these are sample tables and the actual one has thousands of records)

Sales table
| ID | SALESID | ITEM | Details… |
| ------------ | ------------ | ------------ | ------------ |
| 1 | S1 | Item1 | |
| 2 | S1 | Item2 | |
| 3 | S1 | Item3 | |
| 4 | S2 | Item 4 | |
| 5 | S3 | Item 5 | |
| 6 | S3 | Item 6 | |
Invoice table
| INVSALESID | Invoice amount |
| ------------ | ------------ |
| S1 | A |
| S2 | B |
| S3 | C |
| S4 | D |
| S5 | E |
| S6 | F |

I’m hoping to get the invoice amount added to the first record and rest as 0 to avoid duplication when I am taking the sum for further analysis.

Current result:

Select s.*,Invoice_amount 

FROM Sales_table s
join Invoice_table inv on  s.salesid=inv.InvSalesId
where s.ID = (
       select top 1 s.ID  FROM Sales_table s1
       WHERE S.SALESID=S1.SALESID
          
      )

ID	SALESID	ITEM	Invoice amount
1	S1	Item1	A
2	S1	Item2	A
3	S1	Item3	A
4	S2	Item 4	B
5	S3	Item 5	C
6	S3	Item 6	C

Expected result:
| ID | SALESID | ITEM | Invoice amount |
| ------------ | ------------ | ------------ | ------------ |
| 1 | S1 | Item1 | A |
| 2 | S1 | Item2 | 0 |
| 3 | S1 | Item3 | 0 |
| 4 | S2 | Item 4 | B |
| 5 | S3 | Item 5 | C |
| 6 | S3 | Item 6 | 0 |

sql-server

来源：https://stackoverflow.com/questions/76062878/adding-data-from-the-second-table-to-the-first-matching-record-in-first-table-an

1条答案

按热度按时间

oalqel3c1#

Something like this perhaps:

select id, SALESID, item, [Details…], case when row_number() over(partition by salesid order by id) = 1 then [invoice amount] else 0 end
from (
    VALUES  (1, N'S1', N'Item1', NULL)
    ,   (2, N'S1', N'Item2', NULL)
    ,   (3, N'S1', N'Item3', NULL)
    ,   (4, N'S2', N'Item 4', NULL)
    ,   (5, N'S3', N'Item 5', NULL)
    ,   (6, N'S3', N'Item 6', NULL)
) t (ID,SALESID,ITEM,[Details…])
inner join (
    VALUES  (N'S1', 10)
    ,   (N'S2', 20)
    ,   (N'S3', 30)
    ,   (N'S4', 40)
    ,   (N'S5', 50)
    ,   (N'S6', 60)
) t2 (NVSALESID,[Invoice amount])
    ON  t2.nvsalesid = t.SALESID

you partition by salesid and check if it's first row then return amount, otherwise 0

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server Adding data from the second table to the first matching record in first table and setting the rest to zero

1条答案

相关问题

热门标签

最新问答