CODEWITHSUNDEEP
sql-servert-sql
Rex
Rex

Reputation: 521

SQL - SELECT first matching row column value and any subsequent row value as null for specific columns or tables

2 tables with data shown below.

Order table:

| OrderId | TotalAmount | TotalTax |
|---------|-------------|----------|
|    1    |    10       |    0.1   |
|    2    |    40       |    0.4   | 
|    3    |    20       |    0.2   |

Items table:(OrderId FK)

| OrderId | ItemId | ItemDesc |
|---------|--------|----------|
|    1    |    1   |   Item1  |
|    1    |    2   |   Item2  | 
|    2    |    1   |   Item1  | 
|    2    |    3   |   Item3  | 
|    2    |    5   |   Item5  | 
|    3    |    7   |   Item7  | 
|    3    |    5   |   Item5  |

Expected output -Records after joining both tables should have values for matching records in first row and subsequent matching rows should have null for columns from order table. As shown below

| OrderId | TotalAmount | TotalTax |ItemId | ItemDesc |
|---------|-------------|----------|-------|----------|
|    1    |    10       |    0.1   |  1    |   Item1  |
|    1    |    NULL     |    NULL  |  2    |   Item2  |
|    2    |    40       |    0.4   |  1    |   Item1  |
|    2    |    NULL     |    NULL  |  3    |   Item3  |
|    2    |    NULL     |    Null  |  5    |   Item5  |
|    3    |    20       |    0.2   |  7    |   Item7  |
|    3    |    NULL     |    NULL  |  5    |   Item5  |

Query:

DECLARE @order table(orderId int, TotalAmount  int, totaltax decimal)
Insert into @order(orderId, TotalAmount,totaltax)
values 
( 1,10,0.1),
( 2,40,0.4),
( 3,20, 0.2)

DECLARE @ITEMS table(OrderId  int, ItemId  int, ItemDesc nvarchar(50))
Insert into @ITEMS(OrderId, ItemId,ItemDesc)
values 
( 1,1,'Test1'),
( 1,2,'Test2'),
( 2,1,'Test1'),
( 2,3,'Test3'),
( 2,5,'Test5'),
( 3,7,'Test7'),
( 3,5,'Test5')

 select o.*,i.* from @order o
 inner join  @ITEMS I
 on o.orderId = i.orderId

Upvotes: 0

Views: 665

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Like this:

with q as
(
 select o.orderId, o.TotalAmount, o.totaltax, i.ItemId, i.ItemDesc, 
       case when row_number() over (partition by o.orderID order by i.ItemId) = 1 then 1 else 0 end is_first
 from @order o
 inner join  @ITEMS I
   on o.orderId = i.orderId
)
select orderId, 
       case when is_first = 1 then TotalAmount else null end TotalAmount,
       case when is_first = 1 then TotalTax else null end TotalTax,
       ItemId,
       ItemDesc
from q
order by orderId, ItemId

outputs

orderId     TotalAmount TotalTax                                ItemId      ItemDesc
----------- ----------- --------------------------------------- ----------- ---------
1           10          0                                       1           Test1
1           NULL        NULL                                    2           Test2
2           40          0                                       1           Test1
2           NULL        NULL                                    3           Test3
2           NULL        NULL                                    5           Test5
3           20          0                                       5           Test5
3           NULL        NULL                                    7           Test7

(7 rows affected)

Upvotes: 1

Related Questions