locked
Need to be change tale requirement RRS feed

  • Question


  • I have an employee table. As shown in the first table. I want that table as shown in the second table

    So please help me.

    EMP_NAME EMP_ID Hours Date
    A 1 8 1/2/2020
    B 2 5 1/2/2020
    Bharat 3 7 1/2/2020
    C 4 9 1/3/2020
    D 5 3 1/4/2020
    Divya 6 5 1/1/2020
    E 7 5 1/5/2020
    F 8 5 1/6/2020
    G 9 7 1/7/2020
    H 10 7 1/8/2020
    I 11 2 1/9/2020
    Kiran 12 4 1/1/2020
    kumar 13 5 1/1/2020
    Priya 14 3 1/2/2020
    Raju 15 3 1/1/2020
    Swathi 16 2 1/2/2020

    Requirement Table: Output table

    EMP name  Emp ID 1/1/2020 1/2/2020 1/3/2020 1/4/2020 1/5/2020 1/6/2020 1/7/2020 1/8/2020 1/9/2020
    A 1   8              
    B 2   5              
    Bharat 3   7              
    C 4     9            
    D 5       3          
    Divya 6 5                
    E 7         5        
    F 8           5      
    G 9             7    
    H 10               7  
    I 11                 2
    Kiran 12 4                
    kumar 13 5                
    Priya 14   3              
    Raju 15 3                
    Swathi 16   2              

    Wednesday, August 5, 2020 9:49 PM

Answers

  • DECLARE @Employee TABLE (
    	EMP_NAME varchar(50),
    	EMP_ID int,
    	[Hours] int,
    	[Date] date
    );
    
    INSERT INTO @Employee VALUES
    ('A', 1, 8, '1/2/2020'),
    ('B', 2, 5, '1/2/2020'),
    ('Bharat', 3, 7, '1/2/2020'),
    ('C', 4, 9, '1/3/2020'),
    ('D', 5, 3, '1/4/2020'),
    ('Divya', 6, 5, '1/1/2020'),
    ('E', 7, 5, '1/5/2020'),
    ('F', 8, 5, '1/6/2020'),
    ('G', 9, 7, '1/7/2020'),
    ('H', 10, 7, '1/8/2020'),
    ('I', 11, 2, '1/9/2020'),
    ('Kiran', 12, 4, '1/1/2020'),
    ('kumar', 13, 5, '1/1/2020'),
    ('Priya', 14, 3, '1/2/2020'),
    ('Raju', 15, 3, '1/1/2020'),
    ('Swathi', 16, 2, '1/2/2020');
    
    
    SELECT EMP_NAME, EMP_ID, [1/1/2020], [1/2/2020], [1/13/2020], [1/4/2020], [1/5/2020], [1/6/2020], [1/7/2020], [1/8/2020], [1/9/2020]
    FROM (
    	SELECT * 
    	FROM @Employee
    ) AS src
    PIVOT (
    	 MAX([Hours]) 
    	 FOR [Date] IN ([1/1/2020], [1/2/2020], [1/13/2020], [1/4/2020], [1/5/2020], [1/6/2020], [1/7/2020], [1/8/2020], [1/9/2020])
    ) AS pvt;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Priyanka1592 Thursday, August 6, 2020 8:20 AM
    Wednesday, August 5, 2020 10:32 PM
    Answerer
  • You posted to "Power Pivot" forum, so is your question related to this topic? Simply use a Pivot table in MS Excel to get the required result.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Priyanka1592 Thursday, August 6, 2020 5:53 PM
    Thursday, August 6, 2020 7:02 AM

All replies

  • DECLARE @Employee TABLE (
    	EMP_NAME varchar(50),
    	EMP_ID int,
    	[Hours] int,
    	[Date] date
    );
    
    INSERT INTO @Employee VALUES
    ('A', 1, 8, '1/2/2020'),
    ('B', 2, 5, '1/2/2020'),
    ('Bharat', 3, 7, '1/2/2020'),
    ('C', 4, 9, '1/3/2020'),
    ('D', 5, 3, '1/4/2020'),
    ('Divya', 6, 5, '1/1/2020'),
    ('E', 7, 5, '1/5/2020'),
    ('F', 8, 5, '1/6/2020'),
    ('G', 9, 7, '1/7/2020'),
    ('H', 10, 7, '1/8/2020'),
    ('I', 11, 2, '1/9/2020'),
    ('Kiran', 12, 4, '1/1/2020'),
    ('kumar', 13, 5, '1/1/2020'),
    ('Priya', 14, 3, '1/2/2020'),
    ('Raju', 15, 3, '1/1/2020'),
    ('Swathi', 16, 2, '1/2/2020');
    
    
    SELECT EMP_NAME, EMP_ID, [1/1/2020], [1/2/2020], [1/13/2020], [1/4/2020], [1/5/2020], [1/6/2020], [1/7/2020], [1/8/2020], [1/9/2020]
    FROM (
    	SELECT * 
    	FROM @Employee
    ) AS src
    PIVOT (
    	 MAX([Hours]) 
    	 FOR [Date] IN ([1/1/2020], [1/2/2020], [1/13/2020], [1/4/2020], [1/5/2020], [1/6/2020], [1/7/2020], [1/8/2020], [1/9/2020])
    ) AS pvt;


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by Priyanka1592 Thursday, August 6, 2020 8:20 AM
    Wednesday, August 5, 2020 10:32 PM
    Answerer
  • You posted to "Power Pivot" forum, so is your question related to this topic? Simply use a Pivot table in MS Excel to get the required result.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Priyanka1592 Thursday, August 6, 2020 5:53 PM
    Thursday, August 6, 2020 7:02 AM
  •  Hi olaf,

    I cant able pass customised dates in this stored porcedure .

    When i am select @indate is 4(Customs dates) this procedure throghing error .

    So please helpme this proc.

    DECLARE @INDATE   INT   = 4  

    DECLARE @INSTART  NVARCHAR(10)='01/01/2019'      
    DECLARE @INEND   NVARCHAR(10)  ='12/31/2019'    


    DECLARE @STARTDATE  DATETIME      
    DECLARE @ENDDATE  DATETIME      

     IF @INDATE = 4      
     BEGIN      
       IF @INSTART = ''      
        BEGIN      
          SET @INSTART =DATEADD(YYYY,-1,GETDATE())    
        SET @INEND =GETDATE()    

         --SET @INSTART = '01' + CAST(DATEPART(YYYY,DATEADD(YYYY,-1,GETDATE())) AS NVARCHAR(4))      
         --SET @INEND = '01' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4))      
        END      
        IF @INEND = ''      
        BEGIN      
          SET @INEND=DATEADD(YYYY,1,@INSTART)    
        -- SET @INEND = '01' + CAST(CAST(RIGHT(@INSTART,4) AS INT) + 1 AS NVARCHAR(4))      
        END      
     END      

     IF @INDATE = 1      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
      END      
     ELSE      
     IF @INDATE = 2      
       BEGIN      
       SET @ENDDATE = ( SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        IF DATEPART(MM,GETDATE()) = 1      
        BEGIN      
         SET @STARTDATE = (SELECT DATEADD(YEAR, -1, @ENDDATE))      
        END      
        ELSE      
        BEGIN      
         SET @STARTDATE = (SELECT CAST('1/1/' + CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME))      
        END      
       END      
     ELSE      
     IF @INDATE = 3      
      BEGIN      
      SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

        ELSE      
      BEGIN      
       DECLARE @STARTMONTH INT = (SELECT DATEPART(MONTH,@INSTART))    
       --DECLARE @STARTMONTH INT = (SELECT CAST(LEFT(@INSTART,2) AS INT))      
      -- PRINT @STARTMONTH    
       --SELECT DATEPART(YEAR,@INSTART)    

       SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + (SELECT CAST(DATEPART(YEAR,@INSTART) AS NVARCHAR(4))) AS DATETIME)    
       --SET @STARTDATE = CAST(CAST(@STARTMONTH AS NVARCHAR(2)) + '/1/' + RIGHT(@INSTART,4) AS DATETIME)      
      --PRINT  @STARTDATE    

       DECLARE @END_MONTH_YEARE VARCHAR(6)    

       SELECT @END_MONTH_YEARE= CAST(DATEPART(MONTH,@INEND) AS NVARCHAR(2)) +' ' + CAST(DATEPART(YEAR,@INEND) AS NVARCHAR(4))    
      -- PRINT @END_MONTH_YEARE    

      -- PRINT LEN(@END_MONTH_YEARE)    

        IF LEN(@END_MONTH_YEARE) > 0      
         BEGIN      
          IF CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 = 13      
           BEGIN      
            SET @ENDDATE = (SELECT CAST('1/1/' + CAST((CAST(RIGHT(@END_MONTH_YEARE, 4) AS INT) + 1) AS NVARCHAR(4)) AS DATETIME))      
           END      
          ELSE      
           BEGIN      
            SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME)      
           )      
           END      
         END      
        ELSE      
         BEGIN      
          SET @ENDDATE = (SELECT CAST(CAST(CAST(LEFT(@END_MONTH_YEARE,2) AS INT) + 1 AS NVARCHAR(2)) + '/1/' + RIGHT(@END_MONTH_YEARE, 4) AS DATETIME))      
         END      
       END      

      IF @INDATE = 5     
      BEGIN      
      SET @STARTDATE = (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR,GETDATE())-1 AS NVARCHAR(4)) AS DATETIME))    

            set @ENDDATE= (SELECT CAST(CAST(CAST(LEFT('1',2) AS INT)  AS NVARCHAR(2)) + '/1/' + CAST(datepart(YEAR,GETDATE()) AS NVARCHAR(4)) AS DATETIME))    


      --SET @ENDDATE = (SELECT CAST(CAST(DATEPART(MM,GETDATE()) AS NVARCHAR(2)) + '/1/' +  CAST(DATEPART(YYYY,GETDATE()) AS NVARCHAR(4)) AS DATETIME)  )      
      --SET @STARTDATE = (SELECT DATEADD(YEAR, -2, @ENDDATE))      
      END      

    SELECT @ENDDATE AS ENDDATE, @STARTDATE AS STARTDATE

                                                                                      
    Friday, August 7, 2020 6:19 AM