Pivot Table

Question

Pivot Table

Michael M.M. D'Angelo 176

Hi All,
Please I have this one row table that looks like the below schema

/****** Object:  Table [dbo].[LedgerParameters]    Script Date: 09-Mar-22 4:12:24 AM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[LedgerParameters](  
	[NumberPeriodsThis] [smallint] NULL,  
	[PerStartThis01] [date] NULL,  
	[PerStartThis02] [date] NULL,  
	[PerStartThis03] [date] NULL,  
	[PerStartThis04] [date] NULL,  
	[PerStartThis05] [date] NULL,  
	[PerStartThis06] [date] NULL,  
	[PerStartThis07] [date] NULL,  
	[PerStartThis08] [date] NULL,  
	[PerStartThis09] [date] NULL,  
	[PerStartThis10] [date] NULL,  
	[PerStartThis11] [date] NULL,  
	[PerStartThis12] [date] NULL  
) ON [PRIMARY]  
GO  
INSERT [dbo].[LedgerParameters] ([NumberPeriodsThis], [PerStartThis01], [PerStartThis02], [PerStartThis03], [PerStartThis04], [PerStartThis05], [PerStartThis06], [PerStartThis07], [PerStartThis08], [PerStartThis09], [PerStartThis10], [PerStartThis11], [PerStartThis12]) VALUES (12, CAST(N'2022-01-01' AS Date), CAST(N'2022-02-01' AS Date), CAST(N'2022-03-01' AS Date), CAST(N'2022-04-01' AS Date), CAST(N'2022-05-01' AS Date), CAST(N'2022-06-01' AS Date), CAST(N'2022-07-01' AS Date), CAST(N'2022-08-01' AS Date), CAST(N'2022-09-01' AS Date), CAST(N'2022-10-01' AS Date), CAST(N'2022-11-01' AS Date), CAST(N'2022-12-01' AS Date))  
GO

I need a script that can convert the columns into 12 rows in the format below

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

LiHong-MSFT 10,061

Hi @Michael M.M. D'Angelo
Check this query:

SELECT RIGHT(Column_Nmae,2)+' - '+ CONVERT(VARCHAR,StartDate,103) +' - '+CONVERT(VARCHAR,DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate)),103)+' '+DATENAME(MM,StartDate) AS [Month]  
FROM LedgerParameters   
UNPIVOT(StartDate FOR Column_Nmae IN([PerStartThis01], [PerStartThis02], [PerStartThis03], [PerStartThis04], [PerStartThis05], [PerStartThis06],   
                                     [PerStartThis07], [PerStartThis08], [PerStartThis09], [PerStartThis10], [PerStartThis11], [PerStartThis12]))U

If you don't want to use UNPIVOT keyword,you could also try Cross Apply like this:

SELECT RIGHT(Column_Name,2)+' - '+ CONVERT(VARCHAR,StartDate,103) +' - '+CONVERT(VARCHAR,DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate)),103)+' '+DATENAME(MM,StartDate) AS [Month]  
FROM #LedgerParameters CROSS APPLY(VALUES([PerStartThis01],'PerStartThis01'), ([PerStartThis02],'PerStartThis02'), ([PerStartThis03],'PerStartThis03'),   
                                         ([PerStartThis04],'PerStartThis04'), ([PerStartThis05],'PerStartThis05'), ([PerStartThis06],'PerStartThis06'),   
										 ([PerStartThis07],'PerStartThis07'), ([PerStartThis08],'PerStartThis08'), ([PerStartThis09],'PerStartThis09'),   
										 ([PerStartThis10],'PerStartThis10'), ([PerStartThis11],'PerStartThis11'), ([PerStartThis12],'PerStartThis12'))C(StartDate,Column_Name)

Output:

Michael M.M. D'Angelo 176 Reputation points

2022-03-09T13:57:38.707+00:00

Thank you so much, it worked , but which of the two options is more effecient?
Also please how can I have a row number column in front of the month column
Naomi Nosonovsky 8,906 Reputation points

2022-03-09T18:29:11.753+00:00

CROSS APPLY option is usually a bit more flexible. What exactly did you mean by the extra column? What did you want to get (post desired structure)?

Viorel 127K

Consider the third option too:

select * from (values 
( '01 - 01/01/2022 - 31/01/2022 January' ),
( '02 - 01/02/2022 - 28/02/2022 February' ),
( '03 - 01/03/2022 - 31/03/2022 March' ),
. . .
) t(Month)

LiHong-MSFT 10,061

In theory, using CrossApply will be more effecient. if there is not so much data, there is almost no difference between the speed of these two methods.
To add a row number column in front of the month column,Then try this :

SELECT ROW_NUMBER()OVER(ORDER BY StartDate)AS RowNumber,
       RIGHT(Column_Nmae,2)+' - '+ CONVERT(VARCHAR,StartDate,103) +' - '+CONVERT(VARCHAR,DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate)),103)+' '+DATENAME(MM,StartDate) AS [Month]
FROM #LedgerParameters 
UNPIVOT(StartDate FOR Column_Nmae IN([PerStartThis01], [PerStartThis02], [PerStartThis03], [PerStartThis04], [PerStartThis05], [PerStartThis06], 
                                     [PerStartThis07], [PerStartThis08], [PerStartThis09], [PerStartThis10], [PerStartThis11], [PerStartThis12]))U

Share via

Pivot Table

0 additional answers

Your answer