CODEWITHSUNDEEP
sqloracle-database
RutherA
RutherA

Reputation: 19

how to add two columns as result in single query

I have below queries

select a.emp_id,a.AUTH_TYPE,b.NTE_TX as des1
  from table  a inner join NOTES b on b.TBL_ROW_ID=a.emp_id
 where a.emp_id=56 and REF_NTE_TYP_ID=27

Output 1

enter image description here

select a.emp_id,a.AUTH_TYPE,b.NTE_TX as des2
  from table  a inner join NOTES b on b.TBL_ROW_ID=a.emp_id
 where a.emp_id=56 and REF_NTE_TYP_ID=28

Output 2

enter image description here

Expected Output:

enter image description here

please help any one.

Upvotes: 1

Views: 87

Answers (3)

Radagast81
Radagast81

Reputation: 3006

If TBL_ROW_ID, REF_NTE_TYP_ID is unique in your table NOTESyou can also use subqueries:

select a.emp_id,a.AUTH_TYPE
     , (SELECT b.NTE_TX 
          FROM NOTES b 
         WHERE b.TBL_ROW_ID=a.emp_id
           AND REF_NTE_TYP_ID=27) as des1
     , (SELECT b.NTE_TX 
          FROM NOTES b 
         WHERE b.TBL_ROW_ID=a.emp_id
           AND REF_NTE_TYP_ID=28) as des2
  from table a
 where a.emp_id=56

If you have multiple text-entries you might want to concatenate these entries to just print one long text (replace the 2 occurences of SELECT b.NTE_TX with the following):

SELECT RTRIM(XMLAGG(XMLELEMENT(b.NTE_TX, CHR(10)).EXTRACT('//text()') ORDER BY b.NTE_TX).GetClobVal(),CHR(10))

I used CHR(10) as text delimiter, just replace it with any other string you want. And you might also want some other ORDER BYcondition.

Upvotes: 0

user12360965
user12360965

Reputation: 1

With Q1 As (
    select 56 emp_id, 'diple' auth_type 
),
Q2 As (
    Select 'text2' Des2,27 ref_nte_type_id, 56 row_id 
    Union All
    Select 'text1' Des2,28 ref_nte_type_id, 56 row_id 
)

Select Q1.*,Q2.Des2,Q3.Des2 Des1 
    From Q1 
    Inner Join Q2 
    On Q1.Emp_Id=Q2.Row_Id
    And Q2.Ref_Nte_Type_Id=27
    Inner Join Q2 Q3
    On Q1.Emp_Id=Q3.Row_Id
    And Q3.Ref_Nte_Type_Id=28

Upvotes: 0

DineshDB
DineshDB

Reputation: 6193

Simple Aggregation(MAX) will help you to get your expected output.

Try this:

select a.emp_id,a.AUTH_TYPE
    ,MAX(CASE WHEN REF_NTE_TYP_ID=27 THEN b.NTE_TX END) as des1
    ,MAX(CASE WHEN REF_NTE_TYP_ID=28 THEN b.NTE_TX END) as des2
  from table  a inner join NOTES b on b.TBL_ROW_ID=a.emp_id
where a.emp_id=56 and REF_NTE_TYP_ID IN(27,28)
GROUP BY a.emp_id,a.AUTH_TYPE

Upvotes: 1

Related Questions