-1

C.ADDRESS1 || ',' || C.ADDRESS2 || ',' || C.ADDRESS3 

pa.address_line_1,
pa.address_line_2,
pa.address_line_3 from per_addresses_f pa ;

how to join three column as single address column with oracle SQL concatenate

How to write query for joining 3 columns as single column?

Likhitha
  • 19
  • 1
  • 3
  • 2
    As you have done in your question? I don't understand what you are asking...? – Alex Poole Sep 24 '19 at 09:56
  • Show us some sample table data and the expected result. All as formatted text, not images. – jarlh Sep 24 '19 at 09:58
  • pa.address_line_1, – Likhitha Sep 24 '19 at 10:00
  • Hey, not as a comment. Edit the question instead. Add several rows of data. Are there any columns that may have NULL values? – jarlh Sep 24 '19 at 10:06
  • pa.address_line_2, pa.address_line_3 are from table per_addresses_f pa. how can concatenate this three columns into single column as Emergency Address – Likhitha Sep 24 '19 at 10:10
  • Please [**edit your question**](https://stackoverflow.com/posts/58077561/edit) to add your data and expected result as formatted text. I still don't understand quite what you need as your question already shows what you need to do. – Alex Poole Sep 24 '19 at 10:14
  • @Likhitha I added in my answer column names you stated. I also added your desired alias. The concatenation you stated in your question is correct in principle, you just had to implement it inside SQL query – Goran Kutlaca Sep 24 '19 at 10:30
  • This kind of questions are already answered, I believe more than once. For example: https://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle. People to often ask questions without trying to do some research before. Show us the query you have tried to do and what is the problem with it ? – VBoka Sep 24 '19 at 10:59
  • Possible duplicate of [What is the string concatenation operator in Oracle?](https://stackoverflow.com/questions/278189/what-is-the-string-concatenation-operator-in-oracle) – VBoka Oct 26 '19 at 13:10

5 Answers5

1

You can write the concatenation as you've written it, but you have to make sure that all the components are strings. If some of them isn't, put it in TO_CHAR function. It's also good to put an alias to your result of concatenated columns:

SELECT C.ADDRESS1 || ',' || C.ADDRESS2 || ',' || C.ADDRESS3  as concatenated_adress
  FROM your_table c

EDIT : After question was edited with concrete columns that are used, table name and with desired alias stated in question columns:

SELECT pa.address_line_1 || ',' || pa.address_line_2 || ',' || pa.address_line_3
         as "Emergency Address"
  FROM per_addresses_f pa
Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18
0

You can do this:

CONCAT(C.ADDRESS1, ',', C.ADDRESS2, ',', C.ADDRESS3) AS newColumnName
jarlh
  • 42,561
  • 8
  • 45
  • 63
protestator
  • 311
  • 1
  • 15
0

Try this : 

SELECT firstname ||' '|| lastname AS employee_name FROM employee;
Akash Namdev
  • 63
  • 6
0

You can also try nested CONCAT assuming all address_line fields have char datatype:

SELECT CONCAT(CONCAT(CONCAT(pa.address_line_1,','),CONCAT(pa.address_line_2,',')),pa.address_line_3) AS address FROM per_addresses_f pa

But clearly pipe operator as suggested by @Goran Kutlaca is much simpler.

django-unchained
  • 844
  • 9
  • 21
0

There are multiple ways to Concate the column in BIP.

    1.

SELECT pa.address_line_1 || ',' || pa.address_line_2 || ',' || pa.address_line_3 as "Emergency Address" FROM per_addresses_f pa

    2.

SELECT CONCAT(CONCAT(CONCAT(pa.address_line_1,','),CONCAT(pa.address_line_2,',')),pa.address_line_3) AS address FROM per_addresses_f pa

    3.

select CONCAT(pa.address_line_1,',',pa.address_line_2,',',pa.address_line_3) as address FROM per_addresses_f pa

Pulkit Kumar Jain
  • 1
  • 2