动态从oracle表获取数据

发布于 2025-01-27 05:10:38 字数 703 浏览 3 评论 0原文

在下面的情况下需要您的帮助。

Table1
    Columns
        Unique_id   number  
        Name        varchar2(20)
        desc        varchar2(20)
        Column_Name        varchar2(20) /* Contain Column name of Table2 like Col1, Col2, Col3*/
        
Table2
    Columns
        Unique_id   number  FK from Table1
        Col1        varchar2(20)
        Col2        varchar2(20)
        Col3        varchar2(20)

我想编写一个查询以获取以下列。

Name, desc, Column_name, Value

值应来自 table2 。所有的列（Col1，Col2，Col3）可能包含数据，或者只有一列可能包含数据。

需要帮助来获取这一点，我使用解码来获取它。在我的实际SCEANRIO中，大约有200列，还有其他更好（表现）解决问题的方法。

谢谢，维杰

原文

Need your help in the below scenario.

Table1
    Columns
        Unique_id   number  
        Name        varchar2(20)
        desc        varchar2(20)
        Column_Name        varchar2(20) /* Contain Column name of Table2 like Col1, Col2, Col3*/
        
Table2
    Columns
        Unique_id   number  FK from Table1
        Col1        varchar2(20)
        Col2        varchar2(20)
        Col3        varchar2(20)

I want to write a query to get the below columns.

Name, desc, Column_name, Value

The Value should be from Table2.
All the columns(Col1, Col2, Col3) may contain data or only one column may contain data.

Need help in how to get that, i used Decode to get that. In my actual sceanrio there are around 200 such columns are there, is there any other better (performant) way to get solve the issue.

Thanks, Vijay

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

赢得她心 2025-02-03 05:10:38

由于您提到表2中包含一个以上包含值的1列，因此结果行也将不止一个。因此，您可以首先使用联合来生成多行，然后可以与Table1-一起加入。

SELECT T1.Name, T1.desc, T1.Column_name, T2.Value
  FROM Table1 T1
  JOIN (SELECT Unique_id, Col1 Value
          FROM Table2
         WHERE col1 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col2
          FROM Table2
         WHERE col2 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col3
          FROM Table2
         WHERE col3 IS NOT NULL
        ... ) T2      --  Repeat this step for how much columns are there in your table.  
        ON T1.Unique_id = T2.Unique_id;

Since you mentioned that if there is more than 1 column containing values in Table2, resulted rows would also be more than one. So you can use UNION ALL first to generate multiple rows and then can join that with Table1-

SELECT T1.Name, T1.desc, T1.Column_name, T2.Value
  FROM Table1 T1
  JOIN (SELECT Unique_id, Col1 Value
          FROM Table2
         WHERE col1 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col2
          FROM Table2
         WHERE col2 IS NOT NULL
         UNION ALL
        SELECT Unique_id, Col3
          FROM Table2
         WHERE col3 IS NOT NULL
        ... ) T2      --  Repeat this step for how much columns are there in your table.  
        ON T1.Unique_id = T2.Unique_id;

回复收藏 0 原文

~没有更多了~