Fields display in first row only

To remove the repeated ID in your SQL query results, you can modify the CASE statement to only assign the ID to the first row within each group. Currently, the ROW_NUMBER() function is used to assign the ID, but it is not correctly partitioned.

To correctly partition the rows and assign the ID only to the first row within each group, you need to include all the columns that define the grouping in the PARTITION BY clause of the ROW_NUMBER() function. In your case, the grouping columns are t0.docentry, T0.docnum, T0.itemcode, T1.itemcode, and T0.prodname.

Here's the modified SQL query:

SELECT
  CASE WHEN ROW_NUMBER() OVER (PARTITION BY t0.docentry, T0.docnum, T0.itemcode, T1.itemcode, T0.prodname ORDER BY t1.itemcode) = 1
       THEN CAST(t0.DocEntry AS VARCHAR) ELSE '' END AS Id,
  T0.docnum,
  T0.itemcode AS 'Father Item',
  T0.prodname,
  T1.ItemCode AS 'Component Item',
  T0.plannedqty AS [Plan Qty]
FROM owor T0
INNER JOIN wor1 T1 ON T0.DocEntry = T1.DocEntry

By including all the necessary grouping columns in the PARTITION BY clause, the ROW_NUMBER() function will assign the ID only to the first row within each group, eliminating the repetition of the ID in all rows.

Hi jayraj

  I have written below coe but ID is getting repeated in all rows

select 
CASE WHEN ROW_NUMBER() OVER (PARTITION BY t0.docentry,T0.docnum,T0.itemcode,T1.itemcode,T0.prodname ORDER BY t1.itemcode) = 1
         THEN CAST(t0.DocEntry AS VARCHAR) ELSE '' END AS Id,
T0.docnum,
T0.itemcode as 'Father Item',T0.prodname,T1.ItemCode 'Component Item', T0.plannedqty as [Plan Qty]
from owor T0 inner join wor1 T1 on T0.DocEntry = T1.DocEntry

Thanks

To display the fields in the first row only, you can make use of the CASE statement in SQL.

SELECT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN CAST(Id AS VARCHAR) ELSE '' END AS Id,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN CAST(No AS VARCHAR) ELSE '' END AS No,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN [Prod. Status] ELSE '' END AS [Prod. Status],
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN Item ELSE '' END AS Item,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN Product ELSE '' END AS Product,
    ComponentItem,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY Id, No, [Prod. Status], Item, Product ORDER BY ComponentItem) = 1
         THEN CAST(issued AS VARCHAR) ELSE '' END AS issued
FROM YourTable

In this query, the ROW_NUMBER() function is used to assign a unique number to each row within a partition. The PARTITION BY clause is used to define the partition based on the columns Id, No, [Prod. Status], Item, and Product. The ORDER BY clause is used to order the rows within each partition by the ComponentItem column.

The CASE statement is then used to conditionally display the values in the first row only. If the row number is 1 within the partition, the value is displayed; otherwise, an empty string is displayed.