question

vsslasd-3371 avatar image
0 Votes"
vsslasd-3371 asked LiHongMSFT-3908 commented

Pivot Question

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


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered LiHongMSFT-3908 commented

Hi @vsslasd-3371
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.


image.png (12.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @vsslasd-3371
If you got this error: Must declare the table variable "@Appointments".
Try this solution using an temp table:

  ;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 * INTO #TEMP FROM CTE
     
  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'SELECT Rep,'+@PIVOT_Column_Names+'  FROM #TEMP
                      GROUP BY Rep,TYPE,RNum 
                      ORDER BY Rep,RNum,TYPE DESC '
  PRINT @SQL_String
  EXEC (@SQL_String)  
    
  DROP TABLE #TEMP

Best regards,
LiHong

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered JingyangLi edited
  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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered LiHongMSFT-3908 commented

Hi @vsslasd-3371
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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Also, if you got this error: Must declare the table variable "@Appointments".
Try this solution using an temp table:

 ;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 * INTO #TEMP FROM CTE   
    
  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'SELECT Rep,'+@ISNULL_String+' FROM #TEMP 
                      PIVOT (MAX(VALUE) FOR ADate IN('+@PIVOT_Column_Names+'))P
                      Order by Rep,RNum,TYPE DESC '
  --PRINT @SQL_String
  EXEC (@SQL_String)
    
 DROP TABLE #TEMP


0 Votes 0 ·