Display Data

• jagjitsingh

  Ten Centuries

  Points: 1072

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  March 11, 2025 at 10:38 am

  #4556625

  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;

  ---