Help with SQL PIVOT / UNPIVOT

Question

I have a table (source and desired output below) that I need to transpose 2 columns into 2 header rows and 2 additional columns into the data points at each row/column intersection. I feel like this needs either PIVOT or UNPIVOT but I can't figure out which, much less the syntax. Any help anyone can offer is much appreciated. Thanks in advance!

Answer 1

Hi @David Kingston ,

Try this query：

create table source(DataPoint varchar(20),[Date] varchar(10),Segment varchar(10),DataValue int,DataCount int, DataConcat varchar(10))  
insert into source values('DataPoint1','12/31/2021','Segment1',-367,7,'-367(7)')  
insert into source values('DataPoint2','','Segment1',7788,-7,'7788(-7)')  
insert into source values('DataPoint3','','Segment1',-1343,2,'-1343(2)')  
insert into source values('DataPoint4','','Segment1',-699,-4,'-699(-4)')  
insert into source values('DataPoint5','','Segment1',-3132,-5,'-3132(-5)')  
insert into source values('DataPoint6','12/31/2022','Segment1',-4018,3,'-4018(3)')  
  
insert into source values('DataPoint1','12/31/2021','Segment2',8973,13,'8973(13)')  
insert into source values('DataPoint2','','Segment2',-2299,-5,'-2299(-5)')  
insert into source values('DataPoint3','','Segment2',-5189,20,'-5189(20)')  
insert into source values('DataPoint4','','Segment2',-5338,-10,'-5338(-10)')  
insert into source values('DataPoint5','','Segment2',5921,11,'5921(11)')  
insert into source values('DataPoint6','12/31/2022','Segment2',473,19,'473(19)')  
  
insert into source values('DataPoint1','12/31/2021','Segment3',5513,-1,'5513(-1)')  
insert into source values('DataPoint2','','Segment3',-2591,16,'-2591(16)')  
insert into source values('DataPoint3','','Segment3',-860,19,'-860(19)')  
insert into source values('DataPoint4','','Segment3',-1414,3,'-1414(3)')  
insert into source values('DataPoint5','','Segment3',-8520,8,'-8520(8)')  
insert into source values('DataPoint6','12/31/2022','Segment3',-9401,4,'-9410(4)')  
  
insert into source values('DataPoint1','12/31/2021','Segment4',1007,12,'1007(12)')  
insert into source values('DataPoint2','','Segment4',-6796,-4,'-6796(-4)')  
insert into source values('DataPoint3','','Segment4',6588,12,'6588(12)')  
insert into source values('DataPoint4','','Segment4',-3353,4,'-3353(4)')  
insert into source values('DataPoint5','','Segment4',-8497,18,'-8497(18)')  
insert into source values('DataPoint6','12/31/2022','Segment4',-6531,17,'-6531(17)')  
  
--drop table source  
--select * from source  
  
select ' ' as Segment ,  
max(case when DataPoint='DataPoint1'then[Date] end) as DataPoint1 ,  
max(case when DataPoint='DataPoint2'then[Date] end) as DataPoint2,  
max(case when DataPoint='DataPoint3'then[Date] end) as DataPoint3,  
max(case when DataPoint='DataPoint4'then[Date] end) as DataPoint4,  
max(case when DataPoint='DataPoint5'then[Date] end) as DataPoint5,  
max(case when DataPoint='DataPoint6'then[Date] end) as DataPoint6  
from source   
  
union all  
select Segment,  
max(case when DataPoint='DataPoint1' then DataConcat end) as DataPoint1,  
max(case when DataPoint='DataPoint2' then DataConcat end) as DataPoint2,  
max(case when DataPoint='DataPoint3' then DataConcat end) as DataPoint3,  
max(case when DataPoint='DataPoint4' then DataConcat end) as DataPoint4,  
max(case when DataPoint='DataPoint5' then DataConcat end) as DataPoint5,  
max(case when DataPoint='DataPoint6' then DataConcat end) as DataPoint6  
from source  
group by Segment  

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @David Kingston ,

This is the method that contains the pivot:

select distinct ''as Segment,DataPoint1,DataPoint2,DataPoint3,DataPoint4,DataPoint5,DataPoint6  from   
(select DataPoint,[Date],Segment from source) as ccc   
pivot  
(max([Date])for DataPoint  
in (DataPoint1,DataPoint2,DataPoint3,DataPoint4,DataPoint5,DataPoint6))   
AS PVT  
  
union all  
select * from   
(select DataPoint,Segment,DataConcat from source) as ccc  
pivot  
(max(DataConcat)for DataPoint  
in (DataPoint1,DataPoint2,DataPoint3,DataPoint4,DataPoint5,DataPoint6))   
AS PVT  

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "**Comment**".