Round Diff , Invoice Total be displaced

If you don’t need to retain all rows and only want one row per Invoice No, you can use a GROUP BY query:

SELECT 
    [Invoice No],
    SUM(Quantity) AS TotalQuantity,
    SUM([Basic Value]) AS TotalBasicValue,
    SUM([Tax Amount]) AS TotalTaxAmount,
    MAX([Round Diff]) AS [Round Diff],
    MAX([Invoice Total]) AS [Invoice Total]
FROM YourTable
GROUP BY [Invoice No];

This approach aggregates the data, displaying only one row per Invoice No.

Input Data

Output

WITH RankedData AS (
    SELECT 
        [Invoice No],
        Quantity,
        [Basic Value],
        [Tax Amount],
        [Round Diff],
        [Invoice Total],
        ROW_NUMBER() OVER (PARTITION BY [Invoice No] ORDER BY (SELECT NULL)) AS RowNum
    FROM YourTable
)
SELECT 
    [Invoice No],
    Quantity,
    [Basic Value],
    [Tax Amount],
    CASE WHEN RowNum = 1 THEN [Round Diff] ELSE NULL END AS [Round Diff],
    CASE WHEN RowNum = 1 THEN [Invoice Total] ELSE NULL END AS [Invoice Total]
FROM RankedData
ORDER BY [Invoice No], RowNum;

1. ROW_NUMBER() Function :

   • The ROW_NUMBER() function assigns a unique number to each row within a group of rows (PARTITION BY [Invoice No]).
   • The ORDER BY (SELECT NULL) ensures that the rows are numbered arbitrarily within each group. You can replace (SELECT NULL) with a specific column (e.g., Quantity) if you want to control the ordering.

2. CASE Statement :

   • The CASE statement checks if the RowNum is 1 (i.e., the first occurrence of the Invoice No).
   • If RowNum = 1, it displays the Round Diff and Invoice Total. Otherwise, it sets these columns to NULL.

3. WITH Clause (Common Table Expression) :

   • The WITH RankedData clause creates a temporary result set that includes the RowNum column for each row.
   • This makes it easier to filter or conditionally display data in the final SELECT statement.

4. Final Output :

   • The final SELECT statement retrieves all columns but conditionally displays Round Diff and Invoice Total only for the first row of each Invoice No.

To achieve the desired output where Round Diff and Invoice Total are displayed only once for repeated DocNum values, you can use a ROW_NUMBER() function in SQL to identify the first occurrence of each DocNum. Then, you can conditionally display the Round Diff and Invoice Total only for the first row of each group.

To achieve the desired outcome where the Round Diff and Invoice Total are displayed only once for each DocNum, you can leverage SQL to aggregate the data using GROUP BY. Here's an example SQL query based on your provided table structure:

SELECT 
    [Invoice No],
    SUM(Quantity) AS TotalQuantity,
    SUM([Basic Value]) AS TotalBasicValue,
    SUM([Tax Amount]) AS TotalTaxAmount,
    MAX([Round Diff]) AS RoundDiff, -- Displaying the Round Diff only once
    MAX([Invoice Total]) AS InvoiceTotal -- Displaying the Invoice Total only once
FROM 
    YourTableName -- Replace YourTableName with the actual table name
GROUP BY 
    [Invoice No];

In this query:

- We are grouping the data by [Invoice No].

- The SUM function is used to calculate the total Quantity, Basic Value, and Tax Amount for each unique [Invoice No].

- The MAX function is used to display the Round Diff and Invoice Total only once for each [Invoice No].

By running this SQL query on your dataset, you should be able to achieve the desired result by consolidating the Round Diff and Invoice Total values for each unique Invoice No.

If you encounter any issues or need further clarification, feel free to ask!