Fields display in first row only

Question

Hi

I have below Data

select t0.docentry,t0.docnum,
T0.PostDate,T0.Duedate,
T0.itemcode ,T0.prodname,T1.ItemCode 'Component Item', T0.plannedqty as [Plan Qty], from owor T0 inner join wor1 T1 on T0.DocEntry = T1.DocEntry

Id	No	Prod. Status	Item	Product	Component Item	issued
146	24400072	NULL	FG10000081	Aspirin	PM20000023	19.09
146	24400072	NULL	FG10000081	Aspirin	PM20000189	950
146	24400072	NULL	FG10000081	Aspirin	PM20000190	9501
146	24400072	NULL	FG10000081	Aspirin	PM20000191	950
146	24400072	NULL	FG10000081	Aspirin	PM20000192	14.661
146	24400072	NULL	FG10000081	Aspirin	PM20000193	190
146	24400072	NULL	FG10000081	Aspirin	PM30000038	8
146	24400072	NULL	FG10000081	Aspirin	SG10000044	5196

I want to display like it below

Id	No	Prod. Status	Item	Product	Component Item	issued
146	24400072	NULL	FG0000021	Aspirin	P2000003	19.09
					                        P2000019	950
					                        P2000010	9501
					                        P2000011	950
					                        P2000012	14.661
					                        P2000013	190
					                        P3000008	8
					                        S1000004	5196
						                                16828.751

Thanks

Jayraj Chhaya · Answer

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.

Ramco Ramco · Answer

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

Jayraj Chhaya · Answer

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.

Fields display in first row only

Answers (3)