dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Hi Tantely,

Well, you can use  a series of union statements to create a column for each
of your data elements, but it might get unwieldy.

You can  also use the "tablefunc" tools to generate such a cross tab. Its
documented here

https://www.postgresql.org/docs/9.5/static/tablefunc.html

Regards,
Jason


On Tue, Feb 14, 2017 at 9:37 AM, Raminosoa Rabemanantsoa, Tantely <
traminosoa@xxxxxxxxxx> wrote:

> Dear Community,
>
>
> With DHIS2 2.24, I am trying to output raw data in a matrix format. I have
> used SQL View to output the data. Data values table is presented as
> following:
>
>
> *orgUnit UID,  dataElement1, Value, Period*
>
> *orgUnit UID,  dataElement2, Value, Period*
>
> *orgUnit UID,  dataElement3, Value, Period*
>
> *orgUnit UID,  dataElement4, Value, Period*
>
> *...*
>
>
> I have used the following code:
>
>
> select dv.sourceid as organisationunitid, ou.name, ou.shortname, ou.code
> as CodeAC, de.name as intitule, pe.startdate, pe.enddate,
> coc.categoryoptioncomboname, dv.value from datavalue dv inner join
> organisationunit ou on (dv.sourceid=ou.organisationunitid) inner join
> dataelement de on (dv.dataelementid=de.dataelementid) inner join period
> pe on (pe.startdate='2016-02-01') inner join _categoryoptioncomboname coc
> on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) where
> dv.dataelementid in (select dataelementid from datasetmembers where
> datasetid in (select datasetid from dataset where name='dataSetName'));
>
>
>
> I would like to output “raw data” for each orgUnit as following:
>
>
>
> *orgUnit UID,  dataElement1, Value, dataElement2, Value, dataElement3,
> Value, dataElement4,  Value, Period*
>
> *...*
>
>
>
> I have used pivot tables , but it is each time doing data agregation of
> the dataElement.
>
>
>
> How should I arrange the SQL code in order to have this result?
>
>
>
> Kind Regards,
>
> Tantely.
>
> *This message and its attachments are confidential and solely for the
> intended recipients. If received in error, please delete them and notify
> the sender via reply e-mail immediately.*
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049