1. Pivot clause

    Article: AN0001883Updated:

    We can you SQL command PIVOT, in case we want to display data in form of a pivot table.

    In the below example we have class records representing tasks for particular teams with stating implementation month and mandays.

     

    Base classes have the following relation:

     

    The objective is to display data in the following structure:

     

    We can achieve that by means of following query:

    select *
    from
    ( select te.name team, ta.name task, 'm' + convert( varchar(10), datediff( month, dateadd( day, -datepart(day, getdate()) + 1, convert( date, getdate())), ta.month) + 1) m, ta.md
    from {{: class.task :}} ta
    left join {{: class.team :}} te on te.id = ta.team
    where ta.month > =dateadd( day, -datepart(day, getdate()) + 1, convert( date, getdate()))
    and ta.month < dateadd( month, 5 /* pocet mesicu */, dateadd( day, -datepart(day, getdate()) + 1, convert( date, getdate())) )
    ) t
    pivot ( sum(md) for m in (m1, m2, m3, m4, m5)) as x

    Note: The query returns columns m1-m5. Renaming columns is solved at the query display by means of a script for list of records of the query:

    function OnLoad()
    {
    var d = System.DateTime.Now;
    OGColumns.GetByCode('m1').Name = d.ToString('MM-yyyy');
    d = d.AddMonths(1);
    OGColumns.GetByCode('m2').Name = d.ToString('MM-yyyy');
    d = d.AddMonths(1);
    OGColumns.GetByCode('m3').Name = d.ToString('MM-yyyy');
    d = d.AddMonths(1);
    OGColumns.GetByCode('m4').Name = d.ToString('MM-yyyy');
    d = d.AddMonths(1);
    OGColumns.GetByCode('m5').Name = d.ToString('MM-yyyy');
    }

×