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 < [email protected]> 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 : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > > -- Jason P. Pickering email: [email protected] tel:+46764147049
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
