Sql combining two column into one variable

Question

I have a sql command

SELECT A.*,
       K1.AD CONTACTAD,
       K1.SYD CONTACTSYD,
       KR.AD KURUM,
       K2.AD SUPERVISORAD,
       K2.SYD  SUPERVISORSOYAD,
       K3.AD REQUIRERAD,
       K3.SYD REQUIRERSOYAD
FROM  
       TD_ACTIVITY A,
       KK_KS K1,
       KK_KS K2,
       KK_KS K3,
       KK_KR KR
WHERE  A.INS_CONTACT_PERSON = K1.ID
   AND A.SUPERVISOR =  K2.ID
   AND A.REQUIRERID = K3.ID 
   AND A.INSTITUTIONID = KR.ID

I want to combine CONTACTAD and CONTACTSOYAD into one variable like CONTACT.How can i do that?

Have you tried ... [concat](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm)? — Alexander, Feb 26 '14 at 07:15

score 9 · Accepted Answer · answered Feb 26 '14 at 07:18

Use || for concatenating in oracle

SELECT A.*,
       K1.AD || K1.SYD as CONTACT,
       KR.AD KURUM,
       K2.AD SUPERVISORAD,
       K2.SYD  SUPERVISORSOYAD,
       K3.AD REQUIRERAD,
       K3.SYD REQUIRERSOYAD
FROM  
       TD_ACTIVITY A,
       KK_KS K1,
       KK_KS K2,
       KK_KS K3,
       KK_KR KR
WHERE  A.INS_CONTACT_PERSON = K1.ID
   AND A.SUPERVISOR =  K2.ID
   AND A.REQUIRERID = K3.ID 
   AND A.INSTITUTIONID = KR.ID

score 4 · Answer 2 · answered Feb 26 '14 at 07:18

Use Oracle concatenation operator

SELECT A.*,
       K1.AD || ' ' || K1.SYD CONTACT
       KR.AD KURUM,
       K2.AD SUPERVISORAD,
       K2.SYD SUPERVISORSOYAD,
       K3.AD REQUIRERAD,
       K3.SYD REQUIRERSOYAD 
  FROM TD_ACTIVITY A,
       KK_KS K1,
       KK_KS K2,
       KK_KS K3,
       KK_KR KR 
 WHERE A.INS_CONTACT_PERSON = K1.ID 
       AND A.SUPERVISOR =  K2.ID 
       AND A.REQUIRERID = K3.ID 
       AND A.INSTITUTIONID = KR.ID

Dba · Answer 3 · 2014-02-26T07:29:11.167

3

Try like this,

SELECT A.*,
       concat(k1.ad, k1.syd) concat
       kr.ad kurum,
       k2.ad supervisorad,
       k2.syd supervisorsoyad,
       k3.ad requirerad,
       k3.syd requirersoyad
FROM   td_activity A,
       kk_ks k1,
       kk_ks k2,
       kk_ks k3,
       kk_kr kr
WHERE  A.ins_contact_person = k1.ID
AND    A.supervisor         = k2.ID
AND    A.requirerid         = k3.ID
AND    A.institutionid      = kr.ID;

Refer here to know more about CONCAT in Oracle.

edited Feb 26 '14 at 07:29

answered Feb 26 '14 at 07:16

Dba

6,511
1
24
33

@OracleUser, Yeah. I hope it'll work. – Dba Feb 26 '14 at 07:23
Alias only for the final `SELECT`ed expression? probably this `concat(k1.ad, k1.syd) concat` – Maheswaran Ravisankar Feb 26 '14 at 07:24

score 2 · Answer 4 · answered Feb 26 '14 at 07:17

SELECT K1.AD || columnName "CONTACT"

Here columnName is name of column for which alias is CONTACTSOYAD.

You cannot operate on alias name in same SELECT list so below will give error

SELECT CONTACTAD ||  CONTACTSOYAD "CONTACT" , ...rest of columns...
FROM
..rest of query..

score 1 · Answer 5 · answered Feb 26 '14 at 07:16

1

Please try:

(K1.AD||K1.SYD) AS CONTACT

answered Feb 26 '14 at 07:16

TechDo

18,398
3
51
64

score 1 · Answer 6 · answered Feb 26 '14 at 07:18

1

You can try this:

select CONTACTAD || CONTACTSOYAD from emp;

|| is the concatenation operator in Oracle

hope this helps..

answered Feb 26 '14 at 07:18

Sai Avinash

4,683
17
58
96

Sql combining two column into one variable

6 Answers6

Linked