Pivot clause

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