Skip to Content (Press Enter)
2020-01-08 12-23-57
Jitender Gaur
202Views
4Comments
Join two table with Row into column 
Question

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 


0
0
30 Jun 2020
Copy Link
2018-10-29 08-31-03
João Marques
 
MVP

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

0
0
30 Jun 2020
Copy Link
2020-01-08 12-23-57
Jitender Gaur

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. 

 

0
0
30 Jun 2020
Copy Link
2024-12-18 16-06-42
Junaid Syed

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

0
0
30 Jun 2020
Copy Link
2021-02-02 11-55-27
Tushar Panpaliya

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  


0
0
30 Jun 2020
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.