Query Transpose Result

Support - Assistance technique
1

Hello everyone, I’m trying to make a list with SQL View that get information about relationships. I managed to build a query but ended up running into another challenge. Since DHIS2 stores the tracked entity information as a row I intend to have this same information as a column. Would anyone know how to do this transpose with Postgres or tell me an alternative?
relationShip.doc (936 Bytes)

editado
editado1312×210 60.6 KB

What I want is for the red part to be in the form of a column and not a line as it is now.

Thanks in Advance

1 Like
2

Hi @asacur,
One quick question since I know this is easy in spreadsheets, does it need to be in Postgresql or are you fine with exporting this table to a spreadsheet? A function like =TRANSPOSE() is what I’d use in a spreadsheet.

Maybe if you share the query you are using now other experts can help make modifications to it? Thanks!

3

Hi @Gassim, i had shared in the “relationShip.doc”, in any case here it is

SELECT
	r.relationshipid, 
	r.relationshiptypeid, 
	rt.relationshiptypeid, 
	tei.organisationunitid, 
	trackedentityattribute."name" as nome, 
	trackedentityattributevalue."value", 
	organisationunit."name", 
	r.formname, 
	rt."name"
FROM
	relationship AS r
	JOIN
	relationshiptype AS rt
	ON 
		r.relationshiptypeid = rt.relationshiptypeid AND
		rt.uid = 'xMr0hrY2y2z'
	JOIN
	relationshipitem AS rifrom
	ON 
		rifrom.relationshipid = r.relationshipid
	JOIN
	trackedentityinstance AS tei
	ON 
		rifrom.trackedentityinstanceid = tei.trackedentityinstanceid
	INNER JOIN
	trackedentityattributevalue
	ON 
		tei.trackedentityinstanceid = trackedentityattributevalue.trackedentityinstanceid
	INNER JOIN
	trackedentityattribute
	ON 
		trackedentityattributevalue.trackedentityattributeid = trackedentityattribute.trackedentityattributeid
	INNER JOIN
	organisationunit
	ON 
		tei.organisationunitid = organisationunit.organisationunitid

The main objective is that anyone can run it in SQL View and retrieve the information, using it as it is now the end user would have to go to Excel and do this transformation by themselves. That’s what I want to avoid.
Thanks!

1 Like
4

you might need subselect on attribute value (see my genderbis)
or you can play with json functions (see gender)

with raw_data_relationship as (
       select
        relationshiptype.name,
       
        to_relationshipitem.trackedentityinstanceid as relation_to_tei_id , 
      from_relationshipitem.trackedentityinstanceid as relation_from_tei_id ,
       (coalesce(nullif(replace(array_to_string(array(
           select jsonb_build_object(trackedentityattribute.uid, jsonb_build_object('created', teav.created, 'lastUpdated', teav.lastupdated, 'value', teav.value,
                                                                 'storedBy', teav.storedby, 'trackedentityattribute', trackedentityattribute.uid))
           from trackedentityattributevalue teav
           join trackedentityattribute ON trackedentityattribute.trackedentityattributeid = teav.trackedentityattributeid
           where teav.trackedentityinstanceid = to_relationshipitem.trackedentityinstanceid), ','), '}},{', '},'), ''), NULL) )::jsonb as from_attributes,
       
       (coalesce(nullif(replace(array_to_string(array(
           select jsonb_build_object(trackedentityattribute.uid, jsonb_build_object('created', teav.created, 'lastUpdated', teav.lastupdated, 'value', teav.value,
                                                                 'storedBy', teav.storedby, 'trackedentityattribute', trackedentityattribute.uid))
           from trackedentityattributevalue teav
           join trackedentityattribute ON trackedentityattribute.trackedentityattributeid = teav.trackedentityattributeid
           where teav.trackedentityinstanceid = to_relationshipitem.trackedentityinstanceid), ','), '}},{', '},'), ''), NULL) )as to_attributes
       
       from  relationship join relationshiptype ON relationshiptype.relationshiptypeid = relationship.relationshiptypeid
       join relationshipitem as from_relationshipitem ON from_relationshipitem.relationshipitemid = relationship.from_relationshipitemid
       join relationshipitem as to_relationshipitem ON to_relationshipitem.relationshipitemid = relationship.to_relationshipitemid

)
 
 select *, 
 
 raw_data_relationship.from_attributes->'yo33iKOsHfN'->>'value' as origin, 
 raw_data_relationship.from_attributes->'TN0ZUAIq3jr'->>'value' as gender, 

 (select trackedentityattributevalue.value 
  from trackedentityattributevalue 
  join trackedentityattribute on trackedentityattribute.trackedentityattributeid = trackedentityattributevalue.trackedentityattributeid 
  where trackedentityattributevalue.trackedentityinstanceid = relation_to_tei_id and trackedentityattribute.uid ='yo33iKOsHfN' )  as genderbis
 
 from raw_data_relationship limit 10
2 Likes
5

If your attributes are fixed, another option is to manually create a column for each attribute in your query, and to only show the value if the attribute matches the one for that column, eg:
SELECT
…
CASE WHEN nome = ‘NID’ THEN value ELSE NULL END AS NID
CASE WHEN nome = ‘Primeiros Nomes’ THEN value ELSE NULL END AS PrimeirosNomes
etc

If you then put a GROUP BY across the query, the table should be flattened to show single records with a column for each attribute. This approach performs quite efficiently in MySQL, so hopefully also in Postgres.

(I haven’t had time to test any of this, and am writing it from memory, so please forgive me if there are any lglitches in my syntax.)

1 Like