how to get previous year and current year data in coma separated values in sql

• kbhanu15

  SSCarpal Tunnel

  Points: 4493

  More actions

  May 9, 2023 at 1:27 pm

  #4186639

  Hi Team,

  I have data like below and expected output is like this.

  --Expected o/p

  --Jan-2022,Jan-2023,January-2022,01-01-2023,Jan-2023,Jan-2023

  Can you please help me on this.

  USE [Tempdb]
  GO
  /****** Object:  Table [dbo].[tempIssueRange]    Script Date: 09-05-2023 18:53:12 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[tempIssueRange](
  [IssueID] [int] NULL,
  [IssueName] [varchar](110) NULL,
  [IssueDate] [date] NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1024, N'Jan-2022', CAST(N'2022-01-01' AS Date))
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1176, N'January-2022', CAST(N'2022-01-01' AS Date))
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1188, N'Jan-2023', CAST(N'2023-01-01' AS Date))
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1260, N'01-01-2023', CAST(N'2023-01-01' AS Date))
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1312, N'Jan-2023', CAST(N'2023-01-01' AS Date))
  GO
  INSERT [dbo].[tempIssueRange] ([IssueID], [IssueName], [IssueDate]) VALUES (1324, N'Jan-2023', CAST(N'2023-01-01' AS Date))
  GO
  
  select * from tempIssueRange
  
  --Expected o/p
  --Jan-2022,Jan-2023,January-2022,01-01-2023,Jan-2023,Jan-2023

• ratbak

  Ten Centuries

  Points: 1333

  More actions

  May 9, 2023 at 1:36 pm

  #4186644

  You just want to concatenate all the IssueName values? If you don't care about order, you can use STRING_AGG function:

  SELECT STRING_AGG(IssueName,',') AS IssueNames FROM dbo.tempIssueRange;

• kbhanu15

  SSCarpal Tunnel

  Points: 4493

  More actions

  May 9, 2023 at 1:57 pm

  #4186649

  Hi Ratbak,

  Thanks for your reply but order is the only my main problem.

  --Expected o/p : Previours years following by current year data

  --Jan-2022,January-2022,Jan-2023,01-01-2023,Jan-2023,Jan-2023

  Thanks

  Bhanu

• Jeff Moden

  SSC Guru

  Points: 1003872

  More actions

  May 9, 2023 at 5:45 pm

  #4186801

  kbhanu15 wrote:

  Hi Ratbak,

  Thanks for your reply but order is the only my main problem.

  --Expected o/p : Previours years following by current year data --Jan-2022,January-2022,Jan-2023,01-01-2023,Jan-2023,Jan-2023

  Thanks

  Bhanu

  Since this seems new to you, read the documentation...

  https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

  ... which should lead you to doing the following...

   SELECT STRING_AGG(IssueName, ',') WITHIN GROUP (ORDER BY IssueID)
     FROM dbo.tempIssueRange
  ;

  Results:

  

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)