Display Data

Question

Post reply

Display Data

jagjitsingh

Newbie

Points: 0

March 11, 2025 at 9:59 am

#4556607

Hi

I want below data to appear like below using query

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


docnumitemcodelinetotal
34676256020962015400.000000
25605126207700.000000
26900018209800.000000
3467725609003205960.000000
25609015204040.000000
25801002203260.000000

Thanks

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply

Phil Parkin Newbie Points: 0 More actions · Answer 1

The following solution assumes that the data should be ordered by (DocEntry, Itemcode)

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    DocEntry INT NOT NULL
   ,Itemcode NVARCHAR(50) NULL
   ,Linetotal NUMERIC(19, 6) NULL
);

INSERT #Test
(
    DocEntry
   ,Itemcode
   ,Linetotal
)
VALUES
(34676, N'2560209620', 15400.000000)
,(34676, N'2560512620', 7700.000000)
,(34676, N'2690001820', 9800.000000)
,(34677, N'2560900320', 5960.000000)
,(34677, N'2560901520', 4040.000000)
,(34677, N'2580100220', 3260.000000);

WITH ordered
AS (SELECT t.DocEntry
          ,t.Itemcode
          ,t.Linetotal
          ,rn = ROW_NUMBER () OVER (PARTITION BY t.DocEntry ORDER BY t.Itemcode)
    FROM #Test t)
SELECT docnumitemcodelinetotal = CONCAT (IIF(o.rn = 1, CAST (o.DocEntry AS VARCHAR(10)), ''), o.Itemcode, o.Linetotal)
FROM ordered o
ORDER BY o.DocEntry
        ,o.Itemcode;