locked
Dynamic Pivot Report RRS feed

  • Question

  • "Hello!! Can anyone suggest how the holidays can be shown in pivot report and the Days dynamically for Days columns?? I have created a report something as follows":

    Output obtained using union all: (P - Present A - Absent H - Holiday)

    Name - 1 - 2 - 3 - 4 (Days)
    AT - P - null - P - A
    AT - null - H - null - null

    Desired output:

    Name - 1 - 2 - 3 - 4 (Days)
    AT - P - H - P - A

    Friday, July 4, 2014 3:24 PM

Answers

  • can you try

    seems leave_date, CHECKTIME are datetime why do you want min & count for that

    SELECT Distinct userName,
        [1] AS '1',
        [2] AS '2',
        [3] AS '3',
        [4] AS '4',
        [5] AS '5',
        [6] AS '6',
        [7] AS '7',
        [8] AS '8',
        [9] AS '9',
       [10] AS '10',
       [11] AS '11',
       [12] AS '12',
       [13] AS '13',
       [14] AS '14',
       [15] AS '15',
       [16] AS '16',
       [17] AS '17',
       [18] AS '18',
       [19] AS '19',
       [20] AS '20',
       [21] AS '21',
       [22] AS '22',
       [23] AS '23',
       [24] AS '24',
       [25] AS '25',
       [26] AS '26',
       [27] AS '27',
       [28] AS '28',
       [29] AS '29',
       [30] AS '30',
       [31] AS '31'
    
    FROM
    (Select  DAY(leave_date) as Day, leave_date, userName 
    from AttTime inner join User_Info on User_Info.userID = AttTime.userID 
    inner join Holiday on Holiday.leave_date = '2014-06-06' 
    Where datepart(MM, CHECKTIME) = '06'
    union  
    Select  DAY(CheckTime) as Day, CHECKTIME, userName 
    from AttTime inner join User_Info on User_Info.userID = AttTime.userID 
    Where datepart(MM, CHECKTIME) = '06'
    
    )
    
    source
    PIVOT
    (
        min(leave_date)
        FOR Day
        IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
             [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],[31])
    ) AS PivotDay

    Friday, July 4, 2014 4:04 PM
  • Create a temp table with columns 1 to 31 and fill it up. Before you are done, do

    ALTER TABLE #temp DROP COLUMN [31]

    or let the presentation layer take care of the extra columns...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 4, 2014 7:56 PM
  • Thanks A Lot. It worked. I was just messed up with that union operator. Can I make the days To display dynamically like for Feb 28 days, Mar 31 days and so on in the report?? 

    Try this dynamic pivot

    -- temp table to play with for sample 
    drop table #temp1
    select *  into #temp1 from
    ( 
    select 'sra' userName, '2014-07-01' date, 1 val union all
    select 'sra', '2014-07-02', 1 union all
    select 'sra', '2014-07-03', 1 union all
    select 'sra', '2014-07-04', 1 union all
    select 'sra', '2014-07-05', 1 union all
    select 'sra', '2014-07-06', 1 union all
    select 'sra', '2014-07-07', 1 union all
    select 'sra', '2014-07-08', 1 union all
    select 'sra', '2014-07-09', 1 union all
    select 'sra', '2014-07-10', 1 union all
    select 'sra', '2014-07-11', 1 union all
    select 'sra', '2014-07-12', 1 union all
    select 'sra', '2014-07-13', 1 union all
    select 'sra', '2014-07-14', 1 union all
    select 'sra', '2014-07-15', 1 union all
    select 'sra', '2014-07-16', 1 union all
    select 'sra', '2014-07-17', 1 union all
    select 'sra', '2014-07-18', 1 union all
    select 'sra', '2014-07-19', 1 union all
    select 'sra', '2014-07-20', 1 union all
    select 'sra', '2014-07-21', 1 union all
    select 'sra', '2014-07-22', 1 union all
    select 'sra', '2014-07-23', 1 union all
    select 'sra', '2014-07-24', 1 union all
    select 'sra', '2014-07-25', 1 union all
    select 'sra', '2014-07-26', 1 union all
    select 'sra', '2014-07-27', 1 union all
    select 'sra', '2014-07-28', 1 union all
    select 'sra', '2014-07-29', 1 union all
    select 'sra', '2014-07-30', 1 union all
    select 'sra', '2014-07-31', 1 
     )a
    
    
    Declare @sdate datetime,@edate datetime,@monthnumber varchar(12)='07',@yearnumber varchar(12)='2014',@columnnumber varchar(max)='',@i int =0,@sql varchar(max)=''
    select @sdate = CONVERT(datetime,@yearnumber+'-'+@monthnumber+'-'+'01')
    select @edate = DATEADD(mm,1,@sdate)-1
    
    while(datediff(dd,@sdate,@edate) > @i)
    begin
    set @columnnumber += ','+QUOTENAME(day(dateadd(dd,@i,@sdate)))
    set @i= @i+1
    end
    
    
    set @sql = 
    'SELECT Distinct userName'+@columnnumber+'
    
    FROM
    (select DAY(date) as Day,* from #temp1) source
    PIVOT
    (
        min(val)
        FOR Day
        IN ('+STUFF(@columnnumber,1,1,'')+' )
    ) AS PivotDay'
    print @sql
    exec(@sql)

    Thanks

    Saravana Kumar C

    Friday, July 4, 2014 10:20 PM

All replies

  • Try below

    select Name, MAX([1]),MAX([2]),MAX([3]),MAX([4]) from
    (
    select 'AT' Name , 'P'[1] , null [2], 'P' [3], 'A' [4]
     union all
     select 'AT' , null, 'H', null , null
    ) a -- result from you pivot table
    group by name


    if you can provide the select statement we could help to achive this with out extra group by in outer query

    Thanks

    • Edited by SaravanaC Friday, July 4, 2014 3:32 PM
    Friday, July 4, 2014 3:30 PM
  • Thanks for the reply. I am using 3 Tables named User_Info, AttTime (ATTENDANCE Table) and Holiday. I am getting the results but with 2 separate rows as follows:

    Name - 1 - 2 - 3 - 4 (Days)
    AT - P - null - P - A
    AT - null - H - null - null

    I want To display that in one row and the days dynamically like for Feb 28 days, Mar 31 Days: 

    Name - 1 - 2 - 3 - 4 (Days)
    AT - P - H - P - A

    And the sample code is as follows:

    SELECT Distinct userName,
        [1] AS '1',
        [2] AS '2',
        [3] AS '3',
        [4] AS '4',
        [5] AS '5',
        [6] AS '6',
        [7] AS '7',
        [8] AS '8',
        [9] AS '9',
       [10] AS '10',
       [11] AS '11',
       [12] AS '12',
       [13] AS '13',
       [14] AS '14',
       [15] AS '15',
       [16] AS '16',
       [17] AS '17',
       [18] AS '18',
       [19] AS '19',
       [20] AS '20',
       [21] AS '21',
       [22] AS '22',
       [23] AS '23',
       [24] AS '24',
       [25] AS '25',
       [26] AS '26',
       [27] AS '27',
       [28] AS '28',
       [29] AS '29',
       [30] AS '30',
       [31] AS '31'

    FROM
    (Select Distinct DAY(leave_date) as Day, leave_date, userName from AttTime inner join User_Info on User_Info.userID = AttTime.userID inner join Holiday on Holiday.leave_date = '2014-06-06' Where datepart(MM, CHECKTIME) = '06')

    source
    PIVOT
    (
        min(leave_date)
        FOR Day
        IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
             [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],[31])
    ) AS PivotDay


    UNION ALL

    SELECT *
    FROM
    (Select Distinct DAY(CheckTime) as Day, CHECKTIME, userName from AttTime inner join User_Info on User_Info.userID = AttTime.userID Where datepart(MM, CHECKTIME) = '06')

    source
    PIVOT
    (
        count(CheckTime)
        FOR Day
        IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
             [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],[31])
    ) AS PivotDay


    Friday, July 4, 2014 3:50 PM
  • can you try

    seems leave_date, CHECKTIME are datetime why do you want min & count for that

    SELECT Distinct userName,
        [1] AS '1',
        [2] AS '2',
        [3] AS '3',
        [4] AS '4',
        [5] AS '5',
        [6] AS '6',
        [7] AS '7',
        [8] AS '8',
        [9] AS '9',
       [10] AS '10',
       [11] AS '11',
       [12] AS '12',
       [13] AS '13',
       [14] AS '14',
       [15] AS '15',
       [16] AS '16',
       [17] AS '17',
       [18] AS '18',
       [19] AS '19',
       [20] AS '20',
       [21] AS '21',
       [22] AS '22',
       [23] AS '23',
       [24] AS '24',
       [25] AS '25',
       [26] AS '26',
       [27] AS '27',
       [28] AS '28',
       [29] AS '29',
       [30] AS '30',
       [31] AS '31'
    
    FROM
    (Select  DAY(leave_date) as Day, leave_date, userName 
    from AttTime inner join User_Info on User_Info.userID = AttTime.userID 
    inner join Holiday on Holiday.leave_date = '2014-06-06' 
    Where datepart(MM, CHECKTIME) = '06'
    union  
    Select  DAY(CheckTime) as Day, CHECKTIME, userName 
    from AttTime inner join User_Info on User_Info.userID = AttTime.userID 
    Where datepart(MM, CHECKTIME) = '06'
    
    )
    
    source
    PIVOT
    (
        min(leave_date)
        FOR Day
        IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
             [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],[31])
    ) AS PivotDay

    Friday, July 4, 2014 4:04 PM
  • Thanks A Lot. It worked. I was just messed up with that union operator. Can I make the days To display dynamically like for Feb 28 days, Mar 31 days and so on in the report?? 
    Friday, July 4, 2014 4:23 PM
  • Create a temp table with columns 1 to 31 and fill it up. Before you are done, do

    ALTER TABLE #temp DROP COLUMN [31]

    or let the presentation layer take care of the extra columns...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 4, 2014 7:56 PM
  • Thanks A Lot. It worked. I was just messed up with that union operator. Can I make the days To display dynamically like for Feb 28 days, Mar 31 days and so on in the report?? 

    Try this dynamic pivot

    -- temp table to play with for sample 
    drop table #temp1
    select *  into #temp1 from
    ( 
    select 'sra' userName, '2014-07-01' date, 1 val union all
    select 'sra', '2014-07-02', 1 union all
    select 'sra', '2014-07-03', 1 union all
    select 'sra', '2014-07-04', 1 union all
    select 'sra', '2014-07-05', 1 union all
    select 'sra', '2014-07-06', 1 union all
    select 'sra', '2014-07-07', 1 union all
    select 'sra', '2014-07-08', 1 union all
    select 'sra', '2014-07-09', 1 union all
    select 'sra', '2014-07-10', 1 union all
    select 'sra', '2014-07-11', 1 union all
    select 'sra', '2014-07-12', 1 union all
    select 'sra', '2014-07-13', 1 union all
    select 'sra', '2014-07-14', 1 union all
    select 'sra', '2014-07-15', 1 union all
    select 'sra', '2014-07-16', 1 union all
    select 'sra', '2014-07-17', 1 union all
    select 'sra', '2014-07-18', 1 union all
    select 'sra', '2014-07-19', 1 union all
    select 'sra', '2014-07-20', 1 union all
    select 'sra', '2014-07-21', 1 union all
    select 'sra', '2014-07-22', 1 union all
    select 'sra', '2014-07-23', 1 union all
    select 'sra', '2014-07-24', 1 union all
    select 'sra', '2014-07-25', 1 union all
    select 'sra', '2014-07-26', 1 union all
    select 'sra', '2014-07-27', 1 union all
    select 'sra', '2014-07-28', 1 union all
    select 'sra', '2014-07-29', 1 union all
    select 'sra', '2014-07-30', 1 union all
    select 'sra', '2014-07-31', 1 
     )a
    
    
    Declare @sdate datetime,@edate datetime,@monthnumber varchar(12)='07',@yearnumber varchar(12)='2014',@columnnumber varchar(max)='',@i int =0,@sql varchar(max)=''
    select @sdate = CONVERT(datetime,@yearnumber+'-'+@monthnumber+'-'+'01')
    select @edate = DATEADD(mm,1,@sdate)-1
    
    while(datediff(dd,@sdate,@edate) > @i)
    begin
    set @columnnumber += ','+QUOTENAME(day(dateadd(dd,@i,@sdate)))
    set @i= @i+1
    end
    
    
    set @sql = 
    'SELECT Distinct userName'+@columnnumber+'
    
    FROM
    (select DAY(date) as Day,* from #temp1) source
    PIVOT
    (
        min(val)
        FOR Day
        IN ('+STUFF(@columnnumber,1,1,'')+' )
    ) AS PivotDay'
    print @sql
    exec(@sql)

    Thanks

    Saravana Kumar C

    Friday, July 4, 2014 10:20 PM
  • Thanks A Lot.
    Monday, July 7, 2014 4:07 AM

  • Thanks A Lot.
    Monday, July 7, 2014 4:07 AM