CODEWITHSUNDEEP
sqlsql-server-2008-r2
Andyww
Andyww

Reputation: 219

SQL Server 2008 R2 - Combine results

I have a pretty simple table. It has a key column and 4 further columns each denoting a different datatype. The rules are this:

  1. Each row has a completed key value
  2. For each row the 4 remaining columns might be all empty
    or
  3. For each row only 1 of the 4 remaining columns will be populated

What I'm trying to achieve is to write a query which will result in two columns only (the key and the 1 of the 4). Essentially condensing the 4 columns ito one.

The datatypes of the 4 columns is as follows:

nvarchar(255)
numeric(32, 5)
datetime
nvarchar(MAX)

Source Data

╔═════╦═══════════╦══════╦════════════╦════════════════╗
║ key ║   col1    ║ col2 ║    col3    ║      col4      ║
╠═════╬═══════════╬══════╬════════════╬════════════════╣
║   1 ║ some text ║ null ║ null       ║ null           ║
║   2 ║ null      ║ 5    ║ null       ║ null           ║
║   3 ║ null      ║ null ║ null       ║ null           ║
║   4 ║ null      ║ null ║ 23/02/2017 ║ null           ║
║   5 ║ null      ║ null ║ null       ║ much more text ║
╚═════╩═══════════╩══════╩════════════╩════════════════╝

Ideal Output

╔═════╦════════════════╗
║ key ║     newCol     ║
╠═════╬════════════════╣
║   1 ║ some text      ║
║   2 ║ 5              ║
║   3 ║ null           ║
║   4 ║ 23/02/2017     ║
║   5 ║ much more text ║
╚═════╩════════════════╝

Any help appreciated.

Upvotes: 0

Views: 40

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The coalesce() function can work, but only if you convert all the values to strings:

select id,
       coalesce(col1,
                cast(col1 as nvarchar(255)),
                cast(col2 as nvarchar(255)),
                cast(col3 as nvarchar(255))
               ) as col

Upvotes: 2

Arockia Nirmal
Arockia Nirmal

Reputation: 777

You should use isnull. 

select key, isnull(col1,col2,col3,col4) as newCol ......

Using COALESCE may create problem sometimes due to data type precedence. Since COALESCE determines the type of the output based on data type precedence.

Check the below link for more details.

Reference

Upvotes: 0

Asier Azkuenaga
Asier Azkuenaga

Reputation: 1189

You could use COALESCE like this

select id, coalesce(cast(column1 as nvarchar(255)), cast(column2 as nvarchar(255)), cast(coulmn3 as nvarchar(255)), cast(column4 as nvarchar(255)) 
from ...

If they are all NULL then NULL will be returned. Otherwise, if we are guaranteed to have only one of the four columns NOT NULL then this one will be returned.

Upvotes: 1

Related Questions