Share via

SQL Server rows to columns (PIVOT/ UNPIVOT)

Glenn Mateus 21 Reputation points
2021-09-08T14:55:44.96+00:00

I have the following table

130190-image.png

And I want it to go like this:

130273-image.png

Is it possible using SQL Server PIVOT or UNPIVOT functions?

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

Answer accepted by question author

MelissaMa-msft 24,246 Reputation points Moderator
2021-09-09T02:49:20.487+00:00

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:
130534-output.png

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.

Was this answer helpful?

2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,221 Reputation points
    2021-09-09T18:24:18.363+00:00

    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 130863-image.png

    130881-image.png

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.