SQL Server rows to columns (PIVOT/ UNPIVOT)

Question

I have the following table

And I want it to go like this:

Is it possible using SQL Server PIVOT or UNPIVOT functions?

Answer 1

Hi @Glenn Mateus ，

Welcome to Microsoft Q&A!

I could not achieve your requirement using pivot/unpivot function after several attempts.

Is it possible using SQL Server PIVOT or UNPIVOT functions?

So it is NO from my side. But you could wait for any possible solution from other experts.

Please refer below another solution using CROSS APPLY:

create table #test  
(MondayPercent decimal(5,2),  
MondayStartDate datetime,  
MondayUntilDate datetime,  
TuesdayPercent decimal(5,2),  
TuesdayStartDate datetime,  
TuesdayUntilDate datetime,  
WednesdayPercent decimal(5,2),  
WednesdayStartDate datetime,  
WednesdayUntilDate datetime,  
ThursdayPercent decimal(5,2),  
ThursdayStartDate datetime,  
ThursdayUntilDate datetime)  
  
insert into #test values  
(-5.00,'2021-09-07','2022-12-31',-10.00,'2021-09-07','2022-12-31',-15.00,'2021-09-07','2022-12-31',-20.00,'2021-09-07','2022-12-31')  
  
SELECT c.*  
FROM #test  
CROSS APPLY  
(  
  VALUES  
    ('MondayPercent',MondayPercent, MondayStartDate, MondayUntilDate),  
    ('TuesdayPercent',TuesdayPercent, TuesdayStartDate, TuesdayUntilDate),  
	('WednesdayPercent',WednesdayPercent, WednesdayStartDate, WednesdayUntilDate),  
	('ThursdayPercent',ThursdayPercent, ThursdayStartDate, ThursdayUntilDate)  
) c ([day],[value], startdate, untildate)  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Good day @Glenn Mateus and @MelissaMa-msft

I understand that you struggled with solving the issue using UNPIVOT, so here it is for the sake of the discussion/learning :-)

This is how we can solve UNPIVOT on multiple columns (this not necessarily provide the best performance, but if the requirement is to use UNPIVOT, for example during a learning course, then here you have it).

SELECT [Day], [Value],  
 StartDate = CASE  
 WHEN [Day] = 'MondayPercent' THEN MondayStartDate  
 WHEN [Day] = 'TuesdayPercent' THEN TuesdayStartDate  
 WHEN [Day] = 'WednesdayPercent' THEN WednesdayStartDate  
 WHEN [Day] = 'ThursdayPercent' THEN ThursdayStartDate  
 END,  
 UntilDate = CASE  
 WHEN [Day] = 'MondayPercent' THEN MondayUntilDate  
 WHEN [Day] = 'TuesdayPercent' THEN TuesdayUntilDate  
 WHEN [Day] = 'WednesdayPercent' THEN WednesdayUntilDate  
 WHEN [Day] = 'ThursdayPercent' THEN ThursdayUntilDate  
 END  
FROM (  
 select   
 MondayPercent, MondayStartDate, MondayUntilDate,  
 TuesdayPercent, TuesdayStartDate, TuesdayUntilDate,  
 WednesdayPercent, WednesdayStartDate, WednesdayUntilDate,  
 ThursdayPercent, ThursdayStartDate, ThursdayUntilDate  
 from test  
) p    
UNPIVOT([Value] FOR [Day] IN (MondayPercent, TuesdayPercent, WednesdayPercent, ThursdayPercent) )AS unpvt;    
GO  

Is it possible using SQL Server PIVOT or UNPIVOT functions?

The answer to the question is YES - It is possible