Dear Jason, Thank you for this instruction. This is what I am looking for.
Kind Regards, *Tantely Raminosoa* *Data Officer* Management Sciences for Health Antananarivo Madagascar Mobile: 0344280040 E-mail: [email protected] Skype: traminosoa <http://www.msh.org/> Stronger health systems. Greater health impact. <https://www.facebook.com/ManagementSciencesForHealth> <https://twitter.com/MSHHealthImpact> <https://www.youtube.com/user/MSHHealthImpact> www.msh.org On Tue, Feb 14, 2017 at 11:42 AM, Jason Pickering < [email protected]> wrote: > 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 <+46%2076%20414%2070%2049> > -- *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
