dhis2-devs team mailing list archive

["Raminosoa Rabemanantsoa, Tantely" <traminosoa@xxxxxxxxxx>]

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: traminosoa@xxxxxxxxxx
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 <
jason.p.pickering@xxxxxxxxx> 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 <
> 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 <+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.*