SQL Server, how to merge two columns into one column?

Question

I have a SQL query

SELECT TABLE_SCHEMA + TABLE_NAME AS ColumnZ 
FROM information_schema.tables

I want the result should be table_Schema.table_name.

help me please!!

Use `SELECT TABLE_SCHEMA + '.' + TABLE_NAME` .... – marc_s Aug 08 '14 at 16:18 — marc_s, Aug 08 '14 at 16:18

score 19 · Accepted Answer · edited Aug 08 '14 at 16:22

19

Try this:

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS ColumnZ 
FROM information_schema.tables

edited Aug 08 '14 at 16:22

marc_s

answered Aug 08 '14 at 16:15

Adrian

1

Thanks. Very simple '+' oprator compbine coumn in to single column – MSTdev Nov 21 '16 at 06:49

score 6 · Answer 2 · answered Dec 20 '14 at 13:27

6

This code work for you try this....

SELECT Title,
FirstName,
lastName, 
ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(LastName,'') as FullName 
FROM Customer

answered Dec 20 '14 at 13:27

Sunil Acharya

score 5 · Answer 3 · answered Aug 08 '14 at 16:16

5

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ColumnZ FROM information_schema.tables

answered Aug 08 '14 at 16:16

Dai

5

Watch out: `CONCAT` is a **new feature** in SQL Server **2012** and thus might not be available to the person asking – marc_s Aug 08 '14 at 16:17
2

@marc_s I did not know this, it will be useful if I ever have to work with old MSSQL versions, thanks. – Dai Aug 08 '14 at 16:22
5

Being a new feature doesn't make the answer worthy of a downvote, in my opinion. – Gordon Linoff Aug 08 '14 at 16:22
1

@GordonLinoff: just to be absolutely clear - it wasn't my downvote; I agree with you - it's a new feature, but it's correct - for that newer version of SQL Server. No downvote warranted ... – marc_s Aug 08 '14 at 16:23

score 2 · Answer 4 · answered Aug 18 '20 at 07:01

2

From SQL Server 2017 onwards, there is CONCAT_WS operator, to concatenate with separators.

SELECT CONCAT_WS('.', TABLE_SCHEMA ,TABLE_NAME) AS ColumnZ 
FROM information_schema.tables

answered Aug 18 '20 at 07:01

Venkataraman R

1

This is great for handling nulls, where you don't want the extra separators, and can do the same for empty strings in a column if it is wrapped in NULLIF(). – Mark E. Nov 25 '22 at 19:57

4 Answers4