Join two table with Row into column

Hello All, 


I am developing an application in O11 reactive app and in this i want to join 2 tables. In Output, table one I want to take 2 column and from table two all rows will be column in output table. like 


        Table 1                                                              Table 2 (static entity)

Tbl1Id    Country  Code                                       tbl2Id      Name   AllowColumn

1               A1           1                                             1           Col1           True  

2               A2           2                                             2           Col2           False 

3               A3           3                                             3           Col3            True


Output structure will be 

Code    Country   Col1   Col2   Col3 


Hi Jitender,


This is not a common scenario.

Either you do 3 joins with Table 2 (one for Col1, one for Col2, one for Col3) or you would need to resort to PIVOT function from SQL Server which is also error prone.


In any case they are not scalable, meaning if by adding another record on Table 2, you want to add another column to the output, you would have to change the query.


Regards,

João

Hi Jitender, your problem is more related to the database concepts. Below is the link to one of the articles on internet which suggests different options to achieve this. You can probably go through them and figure out which one is supported in OutSystems.

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Thanks,

Junaid

João Marques wrote:

Hi Jitender,


This is not a common scenario.

Either you do 3 joins with Table 2 (one for Col1, one for Col2, one for Col3) or you would need to resort to PIVOT function from SQL Server which is also error prone.


In any case they are not scalable, meaning if by adding another record on Table 2, you want to add another column to the output, you would have to change the query.


Regards,

João

 Yes, you can say.. currently there are 5 rows but in feature it can be increase. and I am saving output result in table 3. so i need to get & set data in table 3. 

 

Jitender Gaur wrote:

Hello All, 


I am developing an application in O11 reactive app and in this i want to join 2 tables. In Output, table one I want to take 2 column and from table two all rows will be column in output table. like 


        Table 1                                                              Table 2 (static entity)

Tbl1Id    Country  Code                                       tbl2Id      Name   AllowColumn

1               A1           1                                             1           Col1           True  

2               A2           2                                             2           Col2           False 

3               A3           3                                             3           Col3            True


Output structure will be 

Code    Country   Col1   Col2   Col3 



Refer this maybe : https://stackoverflow.com/questions/34172593/convert-rows-to-columns-after-inner-join-in-sql-server 


But the performance is surely going to be an issue with this query. Not suitable for real-time fetching. If you at all need this data better to implement snapshots using some timers etc. and then query form snapshots as required. 


Cheers,

Tushar