Pivot Question

vsslasd 556 Reputation points
2022-05-18T23:55:50.73+00:00

I have these data elements: Job Number, Job Description, Rep, Appointment Date

I need to see for a week period of time the appointments which fall within each day.

Here is my code: 

Declare @SDate as date=Convert(date, '2022-05-18')  
  
  
Declare @Period table (PerDesc int, PerDate Date)  
  
Insert into @Period(PerDesc,PerDate)  
Values  
(1,@SDate),  
(2,dateadd(D, 1, @SDate)),  
(3,dateadd(D, 2, @SDate)),  
(4,dateadd(D, 3, @SDate)),  
(5,dateadd(D, 4, @SDate)),  
(6,dateadd(D, 5, @SDate)),  
(7,dateadd(D, 6, @SDate))  
  
Select * from @Period  
  
  
Declare @Appointments table (Rep varchar(25), ADate Date, ATime time, Job varchar(15), JDesc varchar(60))  
  
Insert into @Appointments(Rep, ADate, ATime, Job, JDesc)  
Values  
('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'10:00:00'), '1-444-227', 'Job Description 1'),  
('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'14:00:00'), '3-777-000', 'Job Description 2'),  
('Gilbert', (dateadd(D, 3, @SDate)), Convert(time,'06:00:00'), '1-444-227', 'Job Description 1'),  
('Gilbert', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 3'),  
  
('Taylor', @SDate, Convert(time,'9:15:00'), '1-444-227', 'Job Description 1'),  
('Taylor', (dateadd(D, 1, @SDate)), Convert(time,'08:00:00'), '8-000-000', 'Job Description 5'),  
('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'06:30:00'), '9-200-200', 'Job Description 6'),  
('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'07:45:00'), '5-544-700', 'Job Description 1'),  
('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'10:20:00'), '5-544-700', 'Job Description 1'),  
('Taylor', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 1'),  
('Taylor', (dateadd(D, 6, @SDate)), Convert(time,'08:00:00'), '5-544-700', 'Job Description 1')  
  
  
Select * from @Appointments  
Order by Rep, ADate, ATime, Job

But I'm uncertain as to how to put this into a TSQL pivot output, which would end up looking something similar to this.... Important that each of the seven days be listed at the top, even if there isn't an appointment:

203371-screenshot-2022-05-18-165445.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-19T03:02:11.907+00:00

    Hi @vsslasd
    Please also check this: 

    Declare @SDate as date=Convert(date, '2022-05-18')  
Declare @Period table (PerDesc int, PerDate Date)  
Insert into @Period(PerDesc,PerDate)Values  
 (1,@SDate),  
 (2,dateadd(D, 1, @SDate)),  
 (3,dateadd(D, 2, @SDate)),  
 (4,dateadd(D, 3, @SDate)),  
 (5,dateadd(D, 4, @SDate)),  
 (6,dateadd(D, 5, @SDate)),  
 (7,dateadd(D, 6, @SDate))  
Declare @Appointments table (Rep varchar(25), ADate Date, ATime time, Job varchar(15), JDesc varchar(60))  
Insert into @Appointments(Rep, ADate, ATime, Job, JDesc)Values  
 ('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'10:00:00'), '1-444-227', 'Job Description 1'),  
 ('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'14:00:00'), '3-777-000', 'Job Description 2'),  
 ('Gilbert', (dateadd(D, 3, @SDate)), Convert(time,'06:00:00'), '1-444-227', 'Job Description 1'),  
 ('Gilbert', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 3'),     
 ('Taylor', @SDate, Convert(time,'9:15:00'), '1-444-227', 'Job Description 1'),  
 ('Taylor', (dateadd(D, 1, @SDate)), Convert(time,'08:00:00'), '8-000-000', 'Job Description 5'),  
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'06:30:00'), '9-200-200', 'Job Description 6'),  
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'07:45:00'), '5-544-700', 'Job Description 1'),  
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'10:20:00'), '5-544-700', 'Job Description 1'),  
 ('Taylor', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 1'),  
 ('Taylor', (dateadd(D, 6, @SDate)), Convert(time,'08:00:00'), '5-544-700', 'Job Description 1')  

DECLARE @PIVOT_Column_Names VARCHAR(MAX)  
DECLARE @SQL_String VARCHAR(MAX)  
   
SELECT @PIVOT_Column_Names=ISNULL(@PIVOT_Column_Names + ',','')+'MAX(CASE WHEN ADate='+QUOTENAME(PerDate,'''')+' THEN  VALUE ELSE '''' END) AS '+ QUOTENAME(PerDate)   
FROM @Period GROUP BY PerDate  
--PRINT @PIVOT_Column_Names    
  
SET @SQL_String = N'  
;WITH CTE AS  
(SELECT A.ADate,Rep,A.ATime,C.VALUE,C.TYPE,DENSE_RANK()OVER(PARTITION BY ADate,Rep ORDER BY ATime) AS RNum  
 FROM @Appointments A  
 CROSS APPLY (VALUES(Job,''Job''),(JDesc,''JDesc''))C(VALUE,TYPE)  
)  
SELECT Rep,'+@PIVOT_Column_Names+'  FROM CTE  
GROUP BY Rep,TYPE,RNum   
ORDER BY Rep,RNum,TYPE DESC '  
PRINT @SQL_String  
EXEC (@SQL_String)

    Output:
    203503-image.png

    Best regards,
    LiHong

    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".
    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.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-19T06:38:50.797+00:00

    Hi @vsslasd
    If you don't want to use Aggregate along with Case when, you can also try using PIVOT.
    Check this static query: 

    ;WITH CTE AS  
(  
 SELECT A.ADate,Rep,C.VALUE,C.TYPE,DENSE_RANK()OVER(PARTITION BY ADate,Rep ORDER BY ATime) AS RNum  
 FROM @Appointments A  
 CROSS APPLY (VALUES(Job,'Job'),(JDesc,'JDesc'))C(VALUE,TYPE)  
)  
SELECT Rep  
      ,ISNULL([2022-05-18],'')[2022-05-18]  
      ,ISNULL([2022-05-19],'')[2022-05-19]  
	  ,[2022-05-20],[2022-05-21],[2022-05-22],[2022-05-23],[2022-05-24] --Change to ISNULL like above,if you don't want NULL appear on the output.  
FROM CTE   
PIVOT (MAX(VALUE) FOR ADate IN([2022-05-18],[2022-05-19],[2022-05-20],[2022-05-21],[2022-05-22],[2022-05-23],[2022-05-24]))P  
Order by Rep,RNum,TYPE DESC

    Also, check this dynamic query: 

    DECLARE @PIVOT_Column_Names VARCHAR(MAX)  
DECLARE @SQL_String VARCHAR(MAX)  
DECLARE @ISNULL_String VARCHAR(MAX)  
   
SELECT @PIVOT_Column_Names=ISNULL(@PIVOT_Column_Names + ',','') + QUOTENAME(PerDate)   
FROM @Period GROUP BY PerDate  
--PRINT @PIVOT_Column_Names    
  
SELECT @ISNULL_String=ISNULL(@ISNULL_String + ',','') + 'ISNULL(' + QUOTENAME(PerDate) + ','''')' + QUOTENAME(PerDate)   
FROM @Period GROUP BY PerDate  
--PRINT @ISNULL_String    
  
SET @SQL_String =   
N'WITH CTE AS  
 (SELECT A.ADate,Rep,C.VALUE,C.TYPE,DENSE_RANK()OVER(PARTITION BY ADate,Rep ORDER BY ATime) AS RNum  
  FROM @Appointments A  
  CROSS APPLY (VALUES(Job,''Job''),(JDesc,''JDesc''))C(VALUE,TYPE)  
 )  
 SELECT Rep,'+@ISNULL_String+'  
 FROM CTE   
 PIVOT (MAX(VALUE) FOR ADate IN('+@PIVOT_Column_Names+'))P  
 Order by Rep,RNum,TYPE DESC '  
--PRINT @SQL_String  
EXEC (@SQL_String)

    Best regards,
    LiHong

    1 person found this answer helpful.
  2. Jingyang Li 5,891 Reputation points
    2022-05-19T02:00:01.85+00:00 
     Declare @SDate as date=Convert(date, '2022-05-18')


 Declare @Period table (PerDesc int, PerDate Date)

 Insert into @Period(PerDesc,PerDate)
 Values
 (1,@SDate),
 (2,dateadd(D, 1, @SDate)),
 (3,dateadd(D, 2, @SDate)),
 (4,dateadd(D, 3, @SDate)),
 (5,dateadd(D, 4, @SDate)),
 (6,dateadd(D, 5, @SDate)),
 (7,dateadd(D, 6, @SDate))

 --Select * from @Period


 Declare @Appointments table (Rep varchar(25), ADate Date, ATime time, Job varchar(15), JDesc varchar(60))

 Insert into @Appointments(Rep, ADate, ATime, Job, JDesc)
 Values
 ('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'10:00:00'), '1-444-227', 'Job Description 1'),
 ('Gilbert', (dateadd(D, 2, @SDate)), Convert(time,'14:00:00'), '3-777-000', 'Job Description 2'),
 ('Gilbert', (dateadd(D, 3, @SDate)), Convert(time,'06:00:00'), '1-444-227', 'Job Description 1'),
 ('Gilbert', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 3'),

 ('Taylor', @SDate, Convert(time,'9:15:00'), '1-444-227', 'Job Description 1'),
 ('Taylor', (dateadd(D, 1, @SDate)), Convert(time,'08:00:00'), '8-000-000', 'Job Description 5'),
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'06:30:00'), '9-200-200', 'Job Description 6'),
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'07:45:00'), '5-544-700', 'Job Description 1'),
 ('Taylor', (dateadd(D, 3, @SDate)), Convert(time,'10:20:00'), '5-544-700', 'Job Description 1'),
 ('Taylor', (dateadd(D, 4, @SDate)), Convert(time,'07:00:00'), '5-544-700', 'Job Description 1'),
 ('Taylor', (dateadd(D, 6, @SDate)), Convert(time,'08:00:00'), '5-544-700', 'Job Description 1')


  Select *,row_number()Over(partition by Rep,ADate   Order by ATime ) rn 
  into tempAppointments
  from  @Appointments

declare @ColumnHeaders NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);




Select @ColumnHeaders = STUFF( (SELECT ',' + 'max (Case when ADate='+ quotename( Convert(char(8),PerDate,112),'''') +' then  Job+JDesc  else null end) as ' + Quotename(Convert(char(10),PerDate,120),'[')  + char(10)+char(13)
FROM  @Period
Order by PerDate
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

 --print @ColumnHeaders

 SET @sql = N'Select Rep, '
+ @ColumnHeaders 
+' from tempAppointments t
Group by Rep,rn 
Order by Rep,rn'

 print @sql
 EXEC sp_executesql @sql;

 Drop table tempAppointments;
    0 comments
  3. Olaf Helper 40,741 Reputation points
    2022-05-19T06:16:46.547+00:00

    which would end up looking something similar to this

    The database can provide the required data, but it's always the task of the frontend to format the data as wanted; can't be done on backend = SQL Server.
    Use a reporting engine like SSRS or something similar.

    0 comments