Display Data DocNo only once if repeated

Question

CREATE TABLE [dbo].[Test](
    [DocEntry] [int] NOT NULL,
    [Itemcode] [nvarchar](50) NULL,
    [Linetotal] [numeric](19, 6) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34676, N'2560209620', CAST(15400.000000 AS Numeric(19, 6)))
GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34676, N'2560512620', CAST(7700.000000 AS Numeric(19, 6)))
GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34676, N'2690001820', CAST(9800.000000 AS Numeric(19, 6)))
GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34677, N'2560900320', CAST(5960.000000 AS Numeric(19, 6)))
GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34677, N'2560901520', CAST(4040.000000 AS Numeric(19, 6)))
GO
INSERT [dbo].[Test] ([DocEntry], [Itemcode], [Linetotal]) VALUES (34677, N'2580100220', CAST(3260.000000 AS Numeric(19, 6)))
GO

doc    itemcode   linetotal
34676   2560209620   15400.000000
34676   2560512620   7700.000000
34676   2690001820   9800.000000
34677   2560900320   5960.000000
34677   2560901520   4040.000000
34677   2580100220   3260.000000

I want output like below

doc    itemcode   linetotal
34676   2560209620   15400.000000
   2560512620   7700.000000
   2690001820   9800.000000
34677   2560900320   5960.000000
    2560901520   4040.000000
   2580100220   3260.000000

Sophia Carter · Answer

Thank you for providing the detailed schema and data. Based on the scenario you mentioned, it seems you're looking for a way to display the data with a hierarchy based on the `DocEntry` column. To achieve this display format, you can use SQL to work with the data in the desired way. One approach is to use SQL queries with a combination of functions like `ROW_NUMBER()` and `CASE` statements to show the data as intended. Here's an example query that can help you achieve the desired output: WITH NumberedRows AS ( SELECT DocEntry, Itemcode, Linetotal, ROW_NUMBER() OVER (PARTITION BY DocEntry ORDER BY DocEntry) AS RowNum FROM Test ) SELECT CASE WHEN RowNum = 1 THEN CAST(DocEntry AS NVARCHAR(MAX)) ELSE '' END AS Doc, CASE WHEN RowNum = 1 THEN Itemcode ELSE ' ' + Itemcode END AS Itemcode, Linetotal FROM NumberedRows ORDER BY DocEntry, RowNum; In this query: - The `ROW_NUMBER()` function assigns a unique number to each row within a partition (in this case, based on `DocEntry`). - The `CASE` statements are used to conditionally display the `DocEntry` and `Itemcode` values based on the row number. By running this SQL query against your data, you should be able to generate the output you described: doc itemcode linetotal 34676 2560209620 15400.000000 2560512620 7700.000000 2690001820 9800.000000 34677 2560900320 5960.000000 2560901520 4040.000000 2580100220 3260.000000 I hope this SQL example helps you achieve the desired data display format. If you have any further questions or need clarification, feel free to ask!