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