0

I have a database with the structure:

Region    | OpenServicesID | DFC
K00001    | 1400           | 4
K00002    | 1300           | 3
K00001    | 1200           | 4
K00001    | 1100           | 4
K00002    | 1000           | 4

I have to present the data table in the following format:

Region | OpenServicesID | DFC | OpenServicesID | DFC | OpenServicesID|DFC
K00001 | 1400           | 4   | 1200           | 4   | 1100          | 4
K00002 | 1300           | 3   | 1000           | 4   | Null          |Null
3
  • Is the number of (output) columns fixed or at least has a sensible upper limit (like e.g. 10)? Aug 10 '20 at 12:57
  • Upper limit is fixed. You can take 10 in this case.
    – Prateek
    Aug 10 '20 at 13:09
  • Upper limit is fixed. You can take 10 in this case. ROW_NUMBER() in CTE with partitioning by Region. Static pivot using 10 copies of CTE joined by Region and (rownumber-1) mod 10. Or the same CTE and CROSSTAB().
    – Akina
    Aug 10 '20 at 13:17
0

So, I want to thank all for your answers. The code that I came up with looks like this:

/* This Code section is to take different prefernces (in multiple rows) and put them in one row (with multiple columns)*/
SELECT account_id, A[1] IS_ID1, B[1] IS_TYPE1, C[1] IS_TITLE1, D[1] IS_QUERY1, A[2] IS_ID2, B[2] IS_TYPE2, C[2] IS_TITLE2, D[2] IS_QUERY2,
 A[3] IS_ID3, B[3] IS_TYPE3, C[3] IS_TITLE3, D[3] IS_QUERY3, A[4] IS_ID4, B[4] IS_TYPE4, C[4] IS_TITLE4, D[4] IS_QUERY4, A[5] IS_ID5, B[5] IS_TYPE5, C[5] IS_TITLE5, D[5] IS_QUERY5  
from (
    select account_id, array_agg(id) A, array_agg(type) B, array_agg(title) C, array_agg(query) D
    from temp01
    group by 1
    order by 1) z
/* This is the end of this section*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.