ObjectGears Help

Forum   ObjectGears (Version:

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 *
( 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');

This website is using cookies files to provide services and analyse visits. You agree with that by using this website.     Further information