How can I get rid of redundant values ​​from a column?

Question

This is a sample output.

Let me explain what happens:

The request returns all invoices from year to year along with the products that participate in the invoice.

As you can see, we have two accounts in 2010 ... Invoices - 30463 and 30516. Invoice 30463 has 4 products, the delivery price is 105.88. As you can see, the delivery price is repeated on each product, which causes problems when I calculate the amount at the reporting level. 4 product invoice No. 30463 shipping price of 105.00 in total. I want each delivery price of each invoice to be displayed only once, regardless of the quantity of goods on the invoice. How can i achieve this?

HERE: QUERY:

SELECT DATEPART(year, CustomerInvDetail.sentDate) AS "Year", CustomerInvoice.cuInvoiceID, Product.productName, CustomerQuoteProducts.unitPrice, CustomerQuoteProducts.qty, CustomerQuoteProducts.qty * CustomerQuoteProducts.unitPrice AS "Price", CustomerShipping.shippingPrice FROM CustomerInvoice INNER JOIN CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN CustomerQuote ON CustomerQuote.customerQuoteID = CustomerInvoice.customerQuoteID INNER JOIN CustomerQuoteProducts ON CustomerQuoteProducts.customerQuoteID = CustomerQuote.customerQuoteID INNER JOIN CustomerShipping ON CustomerShipping.customerQuoteID = CustomerInvoice.customerQuoteID INNER JOIN Customer ON Customer.customerID = CustomerQuote.customerID INNER JOIN Product ON CustomerQuoteProducts.productID = Product.productID WHERE (DATEPART(year, CustomerInvDetail.sentDate) BETWEEN 2001 AND 2022) AND (Customer.customerID = 500) 

Answer 1

Is something like that possible?

 case when row_number() over(partition by cuInvoiceId order by newid()) = 1 then shippingPrice end 

Update

What does he do:

• It divides the data into sections depending on the value of cuInvoiceId
• Now, inside this section, we want to list each line, but there is nothing to bind, so I used newid() , which basically means listing these lines randomly.
• And finally, with case ... = 1 I want the very first line to be the one that displays shippingPrice , and all the rest is null .

Answer 2

How about approving the case for the shipping price when it is the first item? I assume that you have a row or some way to determine the first element in the invoice - then

case when lineno = 1, then CustomerShipping.shippingPrice else 0 end