March 11, 2025 at 9:59 am
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
March 11, 2025 at 10:38 am
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;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy