1. Pivot clause

    Artikel: AN0001883Aktualisiert:
    Die vorgegebene Sprachenversion vom Artikeltext wird angezeigt, weil es kein Text von der ausgewählten Sprache und Version gibt.

    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');
    }

×