none
Dynamics Date In Pivot SQL RRS feed

  • Question

  • Below is My Stored Procedure

    ALTER procedure SP_S_BILLEDCHARTS        
    as        
    begin        
            
    select AccountID,AccountName,isnull([Jan-19],0) as 'Jan-19',isnull([Feb-19],0) as 'Feb-19',isnull([Mar-19],0) as 'Mar-19',isnull([Apr-19],0) as 'Apr-19',isnull([May-19],0) as 'May-19'  
    ,isnull([Jun-19],0) as 'Jun-19',isnull([Jul-19],0) as 'Jul-19',isnull([Aug-19],0) as 'Aug-19',isnull([Sep-19],0) as 'Sep-19',isnull([Oct-19],0) as 'Oct-19',  
    isnull([Nov-19],0) as 'Nov-19',isnull([Dec-19],0) as 'Dec-19'  
      
    from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)   
      
      
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Jan-19],[Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19])  
    ) as pivottable;  
      
      
    end 

    Tuesday, December 3, 2019 11:17 AM

Answers

  • Hi ,

    Please check following script .

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select cast(cast(year(getdate())as varchar(4))+'0101' as date)[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    ),cte1 as(
    select LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date]),-1)),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 7:18 AM

All replies

  • >>Below is My Stored Procedure

    And?

    I got error message trying running your sp

    Msg 208, Level 16, State 6, Procedure SP_S_BILLEDCHARTS, Line 5 [Batch Start Line 0]
    Invalid object name 'SP_S_BILLEDCHARTS'.

    See example of dynamic pivot

    CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);




    -- dynamic pivot (SQL Server 2005/2008+)
    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 11:40 AM
    Answerer
  • In my sp I already Hard code the Year Field. Insted of Hard cord i Alredy Select the year from select Statement. I don't have an idea how to show in Pivot Format.
    Tuesday, December 3, 2019 12:34 PM
  • I showed you an example  above, getting dynamic columns by using @pivot_cols   variable.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 12:44 PM
    Answerer
  • Can you please do some help.. Is it possiable to do with my existing SP
    Tuesday, December 3, 2019 1:10 PM
  • DECLARE @pivot_cols NVARCHAR(1000);
    DECLARE @pivot_query NVARCHAR(2000);


    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    INTO #tmp FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)   
      
      
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName
        SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + DateOfMonth
                   FROM #tmp
                   ORDER BY '],[' + DateOfMonth
                   FOR XML PATH('')
                   ), 1, 2, '') + ']'

    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM #tmp ' +
      'PIVOT ' +
      '(MAX(NoOfRecords) FOR DateOfMonth IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 1:16 PM
    Answerer
  • Oh Great Thank you so much.. But I'm facing one more issue. While using Pivot Dynamically Its not Showing the data some month bcz that month dont have the record, even if the record not available its must shows

    19-Apr 19-Jan 19-Jul 19-Jun 19-Mar 19-Oct 19-Sep 19-May
    NULL 5 NULL NULL NULL NULL NULL NULL
    NULL NULL 1 7 NULL 1 NULL 2
    5 NULL NULL 1 1 1 1 1

    Need your assist.Kindly Help me



    Tuesday, December 3, 2019 1:34 PM
  • >>>even if the record not available its must shows

    Show what? 0?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 1:49 PM
    Answerer
  • Yes Show 0
    Tuesday, December 3, 2019 1:57 PM
  • Its not showing. 
    Tuesday, December 3, 2019 2:03 PM
  • Here is a working sample for you:

    create table sick_codes_Test (
     EMP_ID int, EMP_SHORT_NAME varchar(100)
     , SEG_CODE int, 
     NOM_DATE date)
     Insert into sick_codes_Test values
     (1,'aaa',1,'2019-12-01'),
      (1,'aaa',2,'2019-12-02'),
     
        (1,'aaa',4,'2019-12-03'),
         (1,'aaa',6,'2019-11-21'),
          (1,'aaa',7,'2019-11-22'),
           (1,'aaa',4,'2019-11-23')
           ,(2,'bbb',3,'2019-12-02'),
           (2,'bbb',2,'2019-12-03')
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=7
     
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(day, -n+1, getdate() ) dt  
    from(Select n from nums) D(n)
    )
       
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'isnull(max (Case when NOM_DATE='+ quotename( Convert(char(8),dt,112),'''') +' then  SEG_CODE  else null end), 0) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
    FROM  dates
    Order by n
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
          SET @sql = N'Select EMP_ID, EMP_SHORT_NAME, '
    + @ColumnHeaders 
    +' from sick_codes_Test
    Group by EMP_ID, EMP_SHORT_NAME '
     
    --print @sql
     EXEC sp_executesql @sql;
       
     
    drop table sick_codes_Test

    Tuesday, December 3, 2019 4:20 PM
    Moderator
  • Here you go

    DECLARE @pivot_cols NVARCHAR(1000);
    DECLARE @pivot_cols1 NVARCHAR(1000);
    SELECT @pivot_cols1 =
            STUFF((SELECT DISTINCT '],COALESCE('+foo_type +',0)[' + foo_type
                   FROM Foo
                   ORDER BY '],COALESCE('+foo_type +',0)['+ foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';



    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols1 +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 4, 2019 5:43 AM
    Answerer
  • while Excute the Query I get the error

    'There is already an object named '#tmp' in the database.'

    Wednesday, December 4, 2019 5:54 AM
  • Open new query and run 

    DECLARE @pivot_cols NVARCHAR(1000);
    DECLARE @pivot_query NVARCHAR(2000);

    DECLARE @pivot_cols1 NVARCHAR(1000);


    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    INTO #tmp FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = 'Active' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)   
      
      
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName


    SELECT @pivot_cols1 =
            STUFF((SELECT DISTINCT '],COALESCE('+foo_type +',0)[' + foo_type
                   FROM Foo
                   ORDER BY '],COALESCE('+foo_type +',0)['+ foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';
        SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + DateOfMonth
                   FROM #tmp
                   ORDER BY '],[' + DateOfMonth
                   FOR XML PATH('')
                   ), 1, 2, '') + ']'

    SET @pivot_query =
    N'SELECT ' + @pivot_cols1 +
      'FROM #tmp ' +
      'PIVOT ' +
      '(MAX(NoOfRecords) FOR DateOfMonth IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 4, 2019 5:57 AM
    Answerer
  • after eexcute the above query i got an error

    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Apr'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Apr'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jan'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jan'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jul'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jul'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jun'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Jun'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Mar'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Mar'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'May'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'May'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Oct'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Oct'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Sep'.
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'Sep'.

    Wednesday, December 4, 2019 6:27 AM
  • Can you post sample data for testing?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 4, 2019 6:32 AM
    Answerer
  • it does work on my test table

    CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);
    INSERT INTO Foo VALUES('E', 0);
    INSERT INTO Foo VALUES('F', NULL);




    DECLARE @pivot_cols NVARCHAR(1000);
    DECLARE @pivot_cols1 NVARCHAR(1000);

    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';




    SELECT @pivot_cols1 =
            STUFF((SELECT DISTINCT '],COALESCE('+foo_type +',0)[' + foo_type
                   FROM Foo
                   ORDER BY '],COALESCE('+foo_type +',0)['+ foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';
    select   @pivot_cols1

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols1 +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 4, 2019 6:36 AM
    Answerer
  • In my table there is one column name Called "AddedOn" in this column who created date shown. Based on this column im get the count. If suppose there is no record on December means its shows like a 0(Zero) in the month of december
    Wednesday, December 4, 2019 6:52 AM
  • It does  not help me, I need your sample data to test it , please post CREATE TABLE + INSERT INTO  + desired result.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 4, 2019 6:53 AM
    Answerer
  • CREATE TABLE TEST_TABLE
    (
    AccountID NVARCHAR(12) PRIMARY KEY,
    AccountName NVARCHAR(12) ,
    AddedOn DATETIME

    )


    INSERT INTO TEST_TABLE VALUES ('183127266','Test1',2019-01-01)
    INSERT INTO TEST_TABLE VALUES ('183127266','Test1',2019-01-02)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-03-01)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-03-02)
    INSERT INTO TEST_TABLE VALUES ('892555666','Test2',2019-04-01)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-02)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-03)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-04-04)
    INSERT INTO TEST_TABLE VALUES ('503787421','Test3',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-05-01)
    INSERT INTO TEST_TABLE VALUES ('742780278','Test4',2019-09-01)
    INSERT INTO TEST_TABLE VALUES ('466365991','Test5',2019-09-01)
    INSERT INTO TEST_TABLE VALUES ('466365991','Test5',2019-10-01)
    INSERT INTO TEST_TABLE VALUES ('664059982','Test6',2019-10-01)
    INSERT INTO TEST_TABLE VALUES ('664059982','Test6',2019-11-01)
    INSERT INTO TEST_TABLE VALUES ('372853081','Test7',2019-12-01)

    Result I want 12 mont of the year. If suppose 2020 Jan comes Result Show from Feb-2019 to Jan-2020

    AccountID AccountName  Jan-19  Feb-19 Mar-19  Apr-19  May-19  Jun-19  Jul-19  Aug-19  Sep-19  Oct-19 Nov-

    183127266 Test1              2            0           2          4          3           0       0            0           2          2     1

    Like wise shows all the accounts

    Wednesday, December 4, 2019 7:14 AM
  • Hi ,

    Please check following script .

    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select cast(cast(year(getdate())as varchar(4))+'0101' as date)[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    ),cte1 as(
    select LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date]),-1)),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  
    (  
      
    SELECT A.AccountID,A.AccountName,COUNT(MONTH(AAM.Addedon)) as NoOfRecords,  
    LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH(AAM.AddedOn) , -1 ) ),3)+ ''-'' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH(AAM.AddedOn),-1)),2) as DateOfMonth       
    FROM AuditAsmntMaster AAM  
    INNER JOIN Account A ON AAM.AccountID = A.AccountID  
    INNER JOIN RefEntityMaster REM ON AAM.AccountID = REM.AccountID AND AAM.EntityID = REM.EntityID
    where REM.Status = ''Active'' and YEAR(AAM.AddedOn)=year(getdate()) AND AAM.AccountID in(select Distinct AccountID from AuditAsmnt_Billing)     
    group by MONTH(AAM.AddedOn),YEAR(AAM.AddedOn),A.AccountID ,A.AccountName    
      
    )t  
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 7:18 AM
  • Also , I do a simple example . Please try .

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test 
    (AccountID int,
    AccountName varchar(5),
    NoOfRecords int,
    DateOfMonth varchar(10))
    insert into test values 
    (1,'A',3,'Feb-19'),
    (2,'D',4,'Mar-19'),
    (3,'F',83,'Apr-19'),
    (4,'Q',30,'Jun-19')
    go 
    
    declare @ColumnHeaders varchar(max)
    declare @Column1 varchar(max)
    declare @sql varchar(max)
    ;with cte as(
    select cast(cast(year(getdate())as varchar(4))+'0101' as date)[date],1 rn 
    union all 
    select dateadd(month,1,[date]) [date], rn+1 rn  from cte where rn<12
    ),cte1 as(
    select LEFT(DATENAME(MONTH,DATEADD(MONTH,MONTH([date]) , -1 ) ),3)
    + '-' + LEFT(DATENAME(YEAR,DATEADD(MONTH,MONTH([date]),-1)),2) [date],rn
    from cte )
    Select @ColumnHeaders = STUFF( (SELECT '
    ,' + 'isnull('+ quotename([date]) +', 0) ' + Quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ''), 
    @Column1=STUFF( (SELECT ',' +  quotename([date]) 
    FROM  cte1
    Order by rn
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    
    select @sql= 'select AccountID,AccountName'+@ColumnHeaders+' 
     from  test 
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in('+@Column1+')  
    ) as pivottable;  '
     print (@sql)
     exec(@sql)
     /*
     select AccountID,AccountName
    ,isnull([Jan-19], 0) [Jan-19]
    ,isnull([Feb-19], 0) [Feb-19]
    ,isnull([Mar-19], 0) [Mar-19]
    ,isnull([Apr-19], 0) [Apr-19]
    ,isnull([May-19], 0) [May-19]
    ,isnull([Jun-19], 0) [Jun-19]
    ,isnull([Jul-19], 0) [Jul-19]
    ,isnull([Aug-19], 0) [Aug-19]
    ,isnull([Sep-19], 0) [Sep-19]
    ,isnull([Oct-19], 0) [Oct-19]
    ,isnull([Nov-19], 0) [Nov-19]
    ,isnull([Dec-19], 0) [Dec-19] 
     from  test 
    pivot  
    (  
    max(NoOfRecords) for DateOfMonth in([Jan-19],[Feb-19],[Mar-19],[Apr-19],[May-19],[Jun-19],[Jul-19],[Aug-19],[Sep-19],[Oct-19],[Nov-19],[Dec-19])  
    ) as pivottable;  
    AccountID   AccountName Jan-19      Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19
    ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    1           A           0           3           0           0           0           0           0           0           0           0           0           0
    2           D           0           0           4           0           0           0           0           0           0           0           0           0
    3           F           0           0           0           83          0           0           0           0           0           0           0           0
    4           Q           0           0           0           0           0           30          0           0           0           0           0           0
     */

    Best regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 7:22 AM
  • Thank you soooooo much its perfactly works. Really so thank you. Now i got the Clear idea.
    Wednesday, December 4, 2019 7:34 AM
  • Hi ,

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 7:36 AM
  • Sure Thanks Rachel

    Thanks and Regards,

    Jai

    Wednesday, December 4, 2019 7:38 AM
  • You're welcome.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 4, 2019 7:43 AM
  • I have one more issue in different Stored Procedure
    Wednesday, December 4, 2019 11:11 AM
  • CREATE TABLE TEST_TABLE
    (
    AccountID NVARCHAR(12)
    ,AccountName NVARCHAR(12),
    AddedOn DATETIME
    )
    INSERT INTO TEST_TABLE VALUES ('183127266','Test1','2019-01-01')
    ,('183127266','Test1','2019-01-02')
    ,('892555666','Test2','2019-03-01')
    ,('892555666','Test2','2019-03-02')
    ,('892555666','Test2','2019-04-01')
    ,('503787421','Test3','2019-04-02')
    ,('503787421','Test3','2019-04-03')
    ,('503787421','Test3','2019-04-04')
    ,('503787421','Test3','2019-05-01')
    ,('742780278','Test4','2019-05-01')
    ,('742780278','Test4','2019-05-01')
    ,('742780278','Test4','2019-09-01')
    ,('466365991','Test5','2019-09-01')
    ,('466365991','Test5','2019-10-01')
    ,('664059982','Test6','2019-10-01')
    ,('664059982','Test6','2019-11-01')
    ,('372853081','Test7','2019-12-01');
    
     
      
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare  @sql NVARCHAR(4000);
    declare @Param int=12
     
    -- --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(Month, -n+1, Dateadd(month,datediff(month,0,getdate()),0) ) dt  
    from(Select n from nums) D(n)
    )
       
    Select @ColumnHeaders = STUFF( (SELECT ',' + 'isnull(SUM(Case when format(AddedOn,''MMM-yyyy'')='+ quotename(format(dt,'MMM-yyyy'),'''') +' then  1  else null end), 0) as ' + Quotename(format(dt,'MMM-yyyy'),'[')  + char(10)+char(13)
    FROM  dates
    Order by n desc
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
       
     --print @ColumnHeaders
     
          SET @sql = N'Select AccountID, AccountName, '
    + @ColumnHeaders 
    +' from TEST_TABLE
    Group by AccountID, AccountName '
     
    --print @sql
     EXEC sp_executesql @sql;
       
     
    
    drop table TEST_TABLE
    
    /*
    AccountID	AccountName	Jan-2019	Feb-2019	Mar-2019	Apr-2019	May-2019	Jun-2019	Jul-2019	Aug-2019	Sep-2019	Oct-2019	Nov-2019	Dec-2019
    183127266	Test1	2	0	0	0	0	0	0	0	0	0	0	0
    892555666	Test2	0	0	2	1	0	0	0	0	0	0	0	0
    503787421	Test3	0	0	0	3	1	0	0	0	0	0	0	0
    742780278	Test4	0	0	0	0	2	0	0	0	1	0	0	0
    466365991	Test5	0	0	0	0	0	0	0	0	1	1	0	0
    664059982	Test6	0	0	0	0	0	0	0	0	0	1	1	0
    372853081	Test7	0	0	0	0	0	0	0	0	0	0	0	1
    */

    Wednesday, December 4, 2019 3:28 PM
    Moderator
  • Im Facing the issue, After year is changed month is not change

    Result Is showing Like that

    AccountID  AccountName   Jan-19      Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19

    But Actually what i need is

    AccountID  AccountName   Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19  Jan-20

    Can you please assist

    Thursday, January 2, 2020 1:15 PM
  • m Facing the issue, After year is changed month is not change

    Result Is showing Like that

    AccountID  AccountName   Jan-19      Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19

    But Actually what i need is

    AccountID  AccountName   Feb-19      Mar-19      Apr-19      May-19      Jun-19      Jul-19      Aug-19      Sep-19      Oct-19      Nov-19      Dec-19  Jan-20

    Can you please assist

    Thursday, January 2, 2020 1:28 PM