Pivot Query Question

  • I have this pivot query and my background is Oracle not SQLServer...Need two things in the following query

    1. Total At the end

    2. Need to mark "Holiday" as per my holiday calendar entry in pivot output

    Holiday Table Structure

    holiday_date

    holiday_description

    this query generates the output like

    task_id......task_name....1-Nov...2-Nov...3-Nov....31-Nov

    1............ATM Issued....3.......7.......8........10

    now I want to add

    task_id.....task_name.....1-Nov...2-Nov...3-Nov....31-Nov...Total

    1...........ATM Issued.....3.......7.......8........10......need sum of all month of task1

    2...........Chequebooks ...7.......17......87.......19......need sum of all month of task2

    declare @StartOfMonth datetime = '10/1/2010';

    declare @counter datetime = @StartOfMonth;

    declare @sql varchar(MAX) = '';

    declare @columnnames varchar(MAX);

    declare @columnfilter varchar(MAX);

    declare @fieldname varchar(12);

    declare @fieldalias varchar(MAX);

    --First, create a string of dynamic columns, one for each day of the month.

    WHILE (MONTH(@counter) = MONTH(@StartOfMonth))

    BEGIN

    SET @fieldname = '[' + CONVERT(varchar(10), @counter, 101) + ']';

    SET @fieldalias = '[' + CONVERT(VARCHAR(20), datepart(day, @counter), 101) + '-'+ left(CONVERT(VARCHAR(20), datename(MONTH, @counter), 101),3) + ']' ;

    --Wrap the columns in ISNULL(@,0) to avoid having null values for days without tasks.

    SET @columnnames = ISNULL(@columnnames + ',', '') + 'ISNULL(' + @fieldname + ',0) AS ' + @fieldalias;

    --Also create a dynamic list of the Task_Date values to include in the pivot.

    SET @columnfilter = ISNULL(@columnfilter + ',', '') + @fieldname;

    SET @counter = DATEADD(DAY,1,@counter);

    END

    select @columnnames

    --Put it all together into a pivot query.

    set @sql = 'SELECT Task_Name, ' + @columnnames + ' FROM (';

    set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count '

    set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';

    exec (@sql)

  • You can change your @sql part to something like below.I cannot test it since you didn't post your table structure and sample data.

    Notice the new column name "TaskTotal"

    --Put it all together into a pivot query.

    set @sql = 'SELECT Task_Name, ' + @columnnames + ' TaskTotal, FROM (';

    set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count,Sum(D.Task_Count)over(partition by M.Task_Name)TaskTotal'

    set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';

    exec (@sql)

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Thanks for your reply. I did try this

    --Put it all together into a pivot query.

    set @sql = 'SELECT Task_Name, ' + @columnnames + ' , Total FROM (';

    set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count, Sum(D.Task_Count) over(partition by M.Task_Name) AS "Total" '

    set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';

    I have date wise data in detail table and entries are of different month. The purpose of the query is to get the monthly data shows total days in a month wise data and a running total of each task of the particular month.

    After running your query it is giving me the total of the task including other months also..I want the total of the particular month only.

    Ok here are my tables

    Task_Master

    ------------------------

    Task_ID

    Task_Name

    Task_Details

    ------------------

    Task_ID

    Task_Date

    Task_Count

    Holiday Table

    --------------

    Holiday_ID

    Holiday_Date

  • --Put it all together into a pivot query.

    set @sql = 'SELECT Task_Name, ' + @columnnames + ' , Total FROM (';

    set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count, Sum(D.Task_Count) over(partition by M.Task_Name,YOUR MONTH COLUMN) AS "Total" '

    set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • I dont have MONTH COLUMN in my table 🙂 as I am populating days of the month dynamically all i have is TASK_ID, TASK_DATE, TASK_COUNT ...thats it..now how to change query accordingly.

  • joshtheflame (11/14/2010)


    I dont have MONTH COLUMN in my table 🙂 as I am populating days of the month dynamically all i have is TASK_ID, TASK_DATE, TASK_COUNT ...thats it..now how to change query accordingly.

    Get the month from the task_date column using datepart function.

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • do one thing plz...I have the following data in TASK_DETAIL...IN TASK_MASTER..u can create just two records with the following n test..results are strange..it should come as two lines with 31 columns from 1st OCtober to 31st OCtober with the values of task_count in their respective days and 0 if nulls in date and task_id number 1 running total should be "3" and task_id number 2 running total should be "4080"

    task_id.......task_name

    -----------------------

    1 abcd

    2 efgh

    Task_idTask_DateTask_count

    12010-10-01 10

    12010-10-02 20

    22010-10-01 1

    12010-11-01 50

    12010-11-02 40

    22010-11-01 60

    22010-11-02 40

    22010-10-02 2

    12010-10-03 450

    12010-10-04 3600

  • joshtheflame (11/14/2010)


    do one thing plz...I have the following data in TASK_DETAIL...IN TASK_MASTER..u can create just two records with the following n test..results are strange..

    task_id.......task_name

    -----------------------

    1 abcd

    2 efgh

    Task_idTask_DateTask_count

    12010-10-01 10

    12010-10-02 20

    22010-10-01 1

    12010-11-01 50

    12010-11-02 40

    22010-11-01 60

    22010-11-02 40

    22010-10-02 2

    12010-10-03 450

    12010-10-04 3600

    I don't think I am quite following you.

    Can you please post some sample data and the expected o/p ? It's like shooting in the dark right now.

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Sachin,

    Just provided u the sample data. If you notice it has data of task 1 and 2 for the month of October and November.

    In October the sum of task_count for task 2 is 3

    In October the sum of task_count for task 1 is 4080

    run the query against the data I provided u and check the total.

    TASK_MASTER

    --------------

    task_id .....task_name

    1 Abcd

    2 EFGH

    TASK_DETAIL

    ---------------

    Task_id....Task_Date.....Task_count

    12010-10-01 10

    12010-10-02 20

    12010-10-03 450

    12010-10-04 3600

    12010-11-01 50

    12010-11-02 40

    22010-10-01 1

    22010-10-02 2

    22010-11-01 60

    22010-11-02 40

  • create table #tbl (Task_id int,Task_Date datetime,Task_count int)

    insert into #tbl

    select 1,'2010-10-01 00:00:00', 10 union

    select 1,'2010-10-02 00:00:00', 20 union

    select 2,'2010-10-01 00:00:00', 1 union

    select 1,'2010-11-01 00:00:00', 50 union

    select 1,'2010-11-02 00:00:00', 40 union

    select 2,'2010-11-01 00:00:00', 60 union

    select 2,'2010-11-02 00:00:00', 40 union

    select 2,'2010-10-02 00:00:00', 2 union

    select 1,'2010-10-03 00:00:00', 450 union

    select 1,'2010-10-04 00:00:00', 3600

    select * from #tbl order by Task_id,Task_Date

    declare @sql varchar(MAX) = '';

    declare @columnnames varchar(MAX)='';

    select @columnnames=@columnnames + MM from(

    select ',[' + convert(varchar(30),Datename(mm,Task_Date)) + ']'MM from #tbl

    )t group by MM

    print @columnnames

    set @sql = 'SELECT * FROM (';

    set @sql = @sql + 'SELECT Task_id,Datename(MM,Task_Date)MM,Task_Count '

    set @sql = @sql + 'FROM #tbl M) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR MM IN (' + stuff(@columnnames,1,1,'') + ')) AS PivotTable';

    exec (@sql)

    drop table #tbl

    From next time please post the sample data the way I did.

    Gee I should get paid for this one 😉

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Sachin thank you for your efforts and I appologize that I didn't provide you the data the way you mentioned me Next time I will do it accordingly.

    Well through your latest query I am only getting the sum of October and november of both task id's but again you might didnt understand my requirement.

    My desired output is like the following and its only for the given month as date parameter.

    output required.

    Task_Name......1-Oct....2-Oct....3-Oct......31-Oct....Total

    ABCD................40.........50.........10..........20........ 120

    EFGH................10.........20..........30.........40.........100

    The Total Column is the total of the task and sum of 1st Oct to 31-Oct ...I hope you understand me this time...your query is not making 31 columns of different days of the input month...and I want the total of the given month only..:) the cheque in the mail 🙂

  • What about multiple months?How will o/p appear for October and November together in the sample data I have used?

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Just one month at a time. User will pass the parameter for month like Jan or Feb or Mar and according to the month the procedure will get executed and create dynamic columns of the days in the month along with the TOTAL COLUMN at the end for each task and the last column need to add is TOTAL and it will sum up for all dates in the given month of the particular task and place it in TOTAL column. The query I've pasted in my first question it was coming out fine except the total at the end of the given month and the tasks and this is what i am looking for.

  • It will work assuming the query will be executed against just one month .

    create table #tbl (Task_id int,Task_Date datetime,Task_count int)

    insert into #tbl

    select 1,'2010-10-01 00:00:00', 10 union

    select 1,'2010-10-02 00:00:00', 20 union

    select 2,'2010-10-01 00:00:00', 1 union

    select 1,'2010-11-01 00:00:00', 50 union

    select 1,'2010-11-02 00:00:00', 40 union

    select 2,'2010-11-01 00:00:00', 60 union

    select 2,'2010-11-02 00:00:00', 40 union

    select 2,'2010-10-02 00:00:00', 2 union

    select 1,'2010-10-03 00:00:00', 450 union

    select 1,'2010-10-04 00:00:00', 3600

    select * from #tbl order by Task_id,Task_Date

    declare @columnnames varchar(MAX)='';

    declare @StartOfMonth datetime = '10/1/2010';

    declare @counter datetime = @StartOfMonth;

    declare @sql varchar(MAX) = '';

    declare @columnfilter varchar(MAX);

    declare @fieldname varchar(12);

    declare @fieldalias varchar(MAX);

    --First, create a string of dynamic columns, one for each day of the month.

    WHILE (MONTH(@counter) = MONTH(@StartOfMonth))

    BEGIN

    SET @fieldname = '[' + CONVERT(varchar(10), @counter, 101) + ']';

    SET @fieldalias = '[' + CONVERT(VARCHAR(20), datepart(day, @counter), 101) + '-'+ left(CONVERT(VARCHAR(20), datename(MONTH, @counter), 101),3) + ']' ;

    --Wrap the columns in ISNULL(@,0) to avoid having null values for days without tasks.

    SET @columnnames = ISNULL(@columnnames + ',', '') + '' + @fieldname --+ + @fieldalias;

    --Also create a dynamic list of the Task_Date values to include in the pivot.

    SET @columnfilter = ISNULL(@columnfilter + ',', '') + @fieldname;

    SET @counter = DATEADD(DAY,1,@counter);

    END

    set @sql = 'SELECT * FROM (';

    set @sql = @sql + 'SELECT Task_id,Task_Date MM,Task_Count,(select Sum(Task_Count) from #tbl t where datename(mm,Task_Date)=' + '''' + DATEname(mm,@StartOfMonth) +'''' + ' and t.Task_id=M.Task_id )TaskTotal '

    set @sql = @sql + 'FROM #tbl M) as SourceTable ';

    set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR MM IN (' + stuff(@columnnames,1,1,'') + ')) AS PivotTable';

    print @sql

    exec (@sql)

    drop table #tbl

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Sachin great work 🙂 just need four things and it will be perfect cause i am trying to change but it has error...not good with sql though.

    1. I've lost my field Aliases (eg. instead of 2010/10/01 i need 1-Oct, 2-Oct ....31-Oct)

    2. Order by Task_ID

    3. Total At the end instead of begining..

    4. Instead of showing NULL the field value should show "0" in column values

    rest is pefect ..

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply