How to convert this query into PIVOT Table.

Question

Could you please help me to convert into PIVOT TABLE. As I want time in Pivot. How many visits in particular time. I tried to but its not working.  


 SET DATEFIRST 1 ;    
          
     Declare @beginDate int = 0;   
     Declare @pendDate int = 1;  
      
      CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15),  
      VisitTime varchar(15),TotalVisits int)  
      
      
      While (@beginDate < 91 and @pendDate < 90)  
         BEGIN  
          INSERT INTO #Test  
         Select * FROM (        
          Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
          Datepart(wk,DATEADD(day, -1 * @pendDate-6 ,'2017-12-31')),  
          DATENAME(DW,Dateadd(day,-1 *@beginDate,'2017-12-31')),  
       DATEPART(HH,DATEADD(HH,1*@beginDate,'2017-12-31'))  
     --  Count(M.VisitorID)   
         
      
      
      
      From EventsMembershipsDetails M  
     Where  M.Datetime BETWEEN '2017-10-01' and '2017-12-31'  
      
     Group by M.VisitorId  
      ) AS SourceTable  
       SET @beginDate += 1  
       SET @pendDate +=1  
     END  
      
     PIVOT  
     (  
       Count(VisitorID)  
     For VisitTime IN ([0-1],[1-2],[2-3],[3-4],[4-5],[5-6],[6-7],[7-8],[8-9],  
                [9-10],[10-11],[11-12],[12-13],[13-14],[14-15],[15-16],[16-17],  
                [17-18],[18-19],[19-20],[20-21],[21-22],[22-23])   
     ) As VisitsCount  
  
[6p04JtVpm][1]

I just want to show time in Pivot. Now its coming in 1 column.

Accepted Answer

Hi @RXR ,

Which part of code did you get the snapshot? Please point out it if possible.

I tried my side with your newly provided data of EventMembershipsDetails table and my query and it is working.

Please refer the query and output from below:

drop table if exists  #Calendar  
 drop table if exists #Test  
      
  SET DATEFIRST 1 ;    
          
  Declare @beginDate int = 0;   
  Declare @pendDate int = 1;  
              
 CREATE TABLE #Calendar(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
   While (@beginDate < 91 and @pendDate < 90)  
      BEGIN  
       insert into #Calendar  
       Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
       Datepart(wk,DATEADD(day, -1 * @pendDate-6 ,'2017-12-31')),  
       DATENAME(DW,Dateadd(day,1 *@beginDate,'2017-12-31'))  
      
    SET @beginDate += 1  
    SET @pendDate +=1  
  END  
      
 CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15),  
        VisitTime varchar(15),VisitorID int)  
      
  insert Into #Test  
   select A.WeekStart,A.WeekEnd,A.WeekDay, rtrim(cast(DATEPART(HH,Datetime) as char(2)))+'-'+ rtrim(cast(DATEPART(HH,Datetime)+1 as char(2))),VisitorID  
        From #Calendar A   
        LEFT JOIN EventMembershipsDetails B   
        ON A.WeekStart=Datepart(WK,Datetime) AND A.WeekDay=DATENAME(DW,Datetime)  
   
 select * from #Test  
      
 select *  
 from (  
 select WeekStart,WeekEnd,WeekDay,VisitTime,VisitorID from #Test   
   )a  
 PIVOT  
       (  
         count(VisitorID)  
       For VisitTime IN ([0-1],[1-2],[2-3],[3-4],[4-5],[5-6],[6-7],[7-8],[8-9],  
            [9-10],[10-11],[11-12],[12-13],[13-14],[14-15],[15-16],[16-17],  
                 [17-18],[18-19],[19-20],[20-21],[21-22],[22-23],[23-24])   
       ) As pvt    
 ORDER BY pvt.weekstart desc,CASE WHEN WeekDay='Monday' THEN 1  
         WHEN weekday='Tuesday' THEN 2  
         WHEN weekday='Wednesday' THEN 3  
         WHEN weekday='Thursday' THEN 4  
         WHEN weekday='Friday' THEN 5  
         WHEN weekday='Saturday' THEN 6  
         WHEN weekday='Sunday' THEN 7 END DESC

Output:

WeekStart	WeekEnd	WeekDay	VisitTime	VisitorID  
53	52	Sunday	11-12	786  
53	52	Monday	NULL	NULL  
53	52	Tuesday	NULL	NULL  
53	52	Wednesday	10-11	786  
53	52	Thursday	NULL	NULL  
53	52	Friday	11-12	786  
53	52	Saturday	NULL	NULL  
52	51	Sunday	NULL	NULL  
52	51	Monday	NULL	NULL  
52	51	Tuesday	NULL	NULL  
52	51	Wednesday	12-13	786  
52	51	Thursday	NULL	NULL  
52	51	Friday	NULL	NULL  
52	51	Saturday	NULL	NULL  
51	50	Sunday	NULL	NULL  
51	50	Monday	NULL	NULL  
51	50	Tuesday	NULL	NULL  
51	50	Wednesday	11-12	786  
51	50	Thursday	NULL	NULL  
51	50	Friday	11-12	786  
51	50	Saturday	NULL	NULL  
50	49	Sunday	NULL	NULL  
50	49	Monday	12-13	786  
50	49	Tuesday	NULL	NULL  
50	49	Wednesday	11-12	786  
50	49	Thursday	NULL	NULL  
50	49	Friday	NULL	NULL  
50	49	Saturday	NULL	NULL  
49	48	Sunday	NULL	NULL  
49	48	Monday	NULL	NULL  
49	48	Tuesday	NULL	NULL  
49	48	Wednesday	NULL	NULL  
49	48	Thursday	NULL	NULL  
49	48	Friday	NULL	NULL  
49	48	Saturday	NULL	NULL  
48	47	Sunday	NULL	NULL  
48	47	Monday	NULL	NULL  
48	47	Tuesday	NULL	NULL  
48	47	Wednesday	NULL	NULL  
48	47	Thursday	NULL	NULL  
48	47	Friday	NULL	NULL  
48	47	Saturday	NULL	NULL  
47	46	Sunday	NULL	NULL  
47	46	Monday	NULL	NULL  
47	46	Tuesday	NULL	NULL  
47	46	Wednesday	NULL	NULL  
47	46	Thursday	NULL	NULL  
47	46	Friday	NULL	NULL  
47	46	Saturday	NULL	NULL  
46	45	Sunday	NULL	NULL  
46	45	Monday	NULL	NULL  
46	45	Tuesday	NULL	NULL  
46	45	Wednesday	NULL	NULL  
46	45	Thursday	NULL	NULL  
46	45	Friday	NULL	NULL  
46	45	Saturday	NULL	NULL  
45	44	Sunday	NULL	NULL  
45	44	Monday	NULL	NULL  
45	44	Tuesday	NULL	NULL  
45	44	Wednesday	NULL	NULL  
45	44	Thursday	NULL	NULL  
45	44	Friday	NULL	NULL  
45	44	Saturday	NULL	NULL  
44	43	Sunday	NULL	NULL  
44	43	Monday	NULL	NULL  
44	43	Tuesday	NULL	NULL  
44	43	Wednesday	NULL	NULL  
44	43	Thursday	NULL	NULL  
44	43	Friday	NULL	NULL  
44	43	Saturday	NULL	NULL  
43	42	Sunday	NULL	NULL  
43	42	Monday	NULL	NULL  
43	42	Tuesday	NULL	NULL  
43	42	Wednesday	NULL	NULL  
43	42	Thursday	NULL	NULL  
43	42	Friday	NULL	NULL  
43	42	Saturday	NULL	NULL  
42	41	Sunday	NULL	NULL  
42	41	Monday	NULL	NULL  
42	41	Tuesday	NULL	NULL  
42	41	Wednesday	NULL	NULL  
42	41	Thursday	NULL	NULL  
42	41	Friday	NULL	NULL  
42	41	Saturday	NULL	NULL  
41	40	Sunday	NULL	NULL  
41	40	Monday	NULL	NULL  
41	40	Tuesday	NULL	NULL  
41	40	Wednesday	NULL	NULL  
41	40	Thursday	NULL	NULL

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.

Answer

It seems that I need to repeat it for a third time:

 For questions like this, we often recommend that you post the CREATE TABLE statement for your table(s) together with INSERT statements with sample data and then the desired result from that sample. We also like to have a short explanation of the underlying business rules, so that we understand why you want that result.

>

That helps to clarify what you are asking for, and it also makes it easy for us to copy and paste into a query window to develop a tested query.

Here is a sample pivot query. You may note that it does not use the PIVOT keyword - you do yourself a service if you stay way from it. The pattern below is more generic and can easily be adapted to different requirements.

SELECT o.name, 
       COUNT(CASE type_name(c.system_type_id) WHEN 'int' THEN 1 END) AS [int],
       COUNT(CASE type_name(c.system_type_id) WHEN 'smallinint' THEN 1 END) AS [smallint],
       COUNT(CASE type_name(c.system_type_id) WHEN 'tinyint' THEN 1 END) AS [tinyint],
       COUNT(CASE type_name(c.system_type_id) WHEN 'datetime' THEN 1 END) AS [datetime],
       COUNT(CASE type_name(c.system_type_id) WHEN 'varchar' THEN 1 END) AS [varchar],
       COUNT(CASE type_name(c.system_type_id) WHEN 'nvarchar' THEN 1 END) AS [nvarchar],
       COUNT(CASE type_name(c.system_type_id) WHEN 'bit'   THEN 1 END) AS [bit]
FROM   sys.columns c
JOIN   sys.objects o ON c.object_id = o.object_id
GROUP  BY o.name

It is not the most meaningful query, but it returns the number of columns with different data types in the database you run it. By using the system tables, I did not have to include any sample data.

Answer

Hi @RXR ,

Please have a try to modify this part as below and check whether it is working. Thanks.

  insert Into #Test  
   select A.WeekStart,A.WeekStartDate,A.WeekDay, rtrim(cast(DATEPART(HH,B.DateTime) as char(2)))+'-'+   
   rtrim(cast(DATEPART(HH,B.Datetime)+1 as char(2))),B.VisitorId,B.VisitorId as PeopleID  
        From #Calendar A   
        inner JOIN  EventMembershipsDetails B   
        ON A.WeekStart = DatePart(Wk,B.DateTime) AND A.WeekDay = DATENAME(DW,B.Datetime)    
		and a.WeekStartDate=CAST(B.Datetime AS date)

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.

Answer

Hi @RXR ,

It could be better to provide the DDL of EventsMembershipsDetails table together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Only according to your limited information provided,please refer below and check whether it is helpful to you.

drop table if exists  #Calendar  
drop table if exists #Test  
  
 SET DATEFIRST 1 ;    
      
 Declare @beginDate int = 0;   
 Declare @pendDate int = 1;  
          
CREATE TABLE #Calendar(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
  While (@beginDate < 91 and @pendDate < 90)  
     BEGIN  
      insert into #Calendar  
      Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
      Datepart(wk,DATEADD(day, -1 * @pendDate-6 ,'2017-12-31')),  
      DATENAME(DW,Dateadd(day,1 *@beginDate,'2017-12-31'))  
  
   SET @beginDate += 1  
   SET @pendDate +=1  
 END  
  
CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15),  
       VisitTime varchar(15),VisitorID int)  
  
 insert Into #Test  
  select A.WeekStart,A.WeekEnd,A.WeekDay, rtrim(cast(DATEPART(HH,Datetime) as char(2)))+'-'+ rtrim(cast(DATEPART(HH,Datetime)+1 as char(2))),VisitorID  
       From #Calendar A   
	   LEFT JOIN EventsMembershipsDetails B   
	   ON A.WeekStart=Datepart(WK,Datetime) AND A.WeekDay=DATENAME(DW,Datetime)  
  
select *  
from (  
select WeekStart,WeekEnd,WeekDay,VisitTime,VisitorID from #Test   
  )a  
PIVOT  
      (  
        count(VisitorID)  
      For VisitTime IN ([0-1],[1-2],[2-3],[3-4],[4-5],[5-6],[6-7],[7-8],[8-9],  
           [9-10],[10-11],[11-12],[12-13],[13-14],[14-15],[15-16],[16-17],  
                [17-18],[18-19],[19-20],[20-21],[21-22],[22-23],[23-24])   
      ) As pvt    
ORDER BY pvt.weekstart desc,CASE WHEN WeekDay='Monday' THEN 1  
		WHEN weekday='Tuesday' THEN 2  
		WHEN weekday='Wednesday' THEN 3  
		WHEN weekday='Thursday' THEN 4  
		WHEN weekday='Friday' THEN 5  
		WHEN weekday='Saturday' THEN 6  
		WHEN weekday='Sunday' THEN 7 END DESC

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.

Answer

Hi @MelissaMa-MSFT

I'm trying to get week using ISO_Week. The issue is all dates are there but 1 day is missing like 25-Dec .

  SET DATEFIRST 1 ;    
              
   Declare @beginDate int = 0;   
   Declare @pendDate int = 1;  
   Declare @CurrDate date = '2017-12-31';  
   Declare @PrevDate date = '2017-12-31';  
   Declare @begino int = 40;  
   Declare @pendno int = 41;   
  
        
  CREATE TABLE #Calendar([WeekStart] varchar(15),[WeekStartDate] varchar(15),[Month] varchar(15),[WeekDay] varchar(15))  
  
    While (@beginDate < @begino and @pendDate < @pendno)  
       BEGIN  
      insert into #Calendar  
        Select Datepart(ISO_WEEK,DATEADD(day, -1 * @beginDate , @CurrDate)),  
        DATEADD(day, -1 * @beginDate , @CurrDate),  
 DATENAME(MM,Dateadd(day, -1 *@beginDate,@CurrDate)),  
        DATENAME(DW,Dateadd(day, -1 *@beginDate,@CurrDate))  
  
  
          
     SET @beginDate += 1  
     SET @pendDate +=1  
   END  
      
  
  CREATE TABLE #Test(WeekStart varchar(15),WeekStartDate date,[Month] varchar(15), [WeekDay] varchar(15),  
         VisitTime varchar(15),PeopleID int,VisitorID int)  
      
   insert Into #Test  
     select A.WeekStart,A.WeekStartDate,A.[Month],A.[WeekDay],   
  rtrim(cast(DATEPART(HH,B.DateTime) as char(2)))+'-'+   
     rtrim(cast(DATEPART(HH,B.Datetime)+1 as char(2))),  
  B.VisitorId,B.VisitorId as PeopleID  
         From #Calendar A   
         inner JOIN  Sample3 B  
  
         ON    
  A.WeekStart = DatePart(ISOWW,B.DateTime) AND   
  A.WeekDay = DATENAME(DW,B.Datetime)    
        And A.WeekStartDate = CAST(B.Datetime AS date)  
          
  select *  
  from (  
  select  WeekStart,WeekStartDate,[Month],WeekDay,VisitTime,VisitorID,PeopleID from #Test   
    )a  
  PIVOT  
        (  
          count(VisitorID)  
        For VisitTime IN ([0-1],[1-2],[2-3],[3-4],[4-5],[5-6],[6-7],[7-8],[8-9],  
             [9-10],[10-11],[11-12],[12-13],[13-14],[14-15],[15-16],[16-17],  
                  [17-18],[18-19],[19-20],[20-21],[21-22],[22-23],[23-24])   
        ) As pvt    
  ORDER BY pvt.WeekStart desc,  
   CASE WHEN WeekDay ='Monday' THEN 1  
          WHEN weekday='Tuesday' THEN 2  
          WHEN weekday='Wednesday' THEN 3  
          WHEN weekday='Thursday' THEN 4  
          WHEN weekday='Friday' THEN 5  
          WHEN weekday='Saturday' THEN 6  
          WHEN weekday='Sunday' THEN 7 END DESC

Share via

How to convert this query into PIVOT Table.

4 additional answers

Your answer