Working with functions to Concatenate columns

Pinal Dave, Blogger and Speaker posted about SQL SERVER – Puzzle – Working with functions to Concatenate columns in which he asked to show a method to concatenate columns of different data types that take care of NULL as well

The best way I can think of is to use CONCAT function introduced in version 2012

emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL

INSERT INTO #temp VALUES( ‘SQL’, NULL, ‘Authority’ );
INSERT INTO #temp VALUES( ‘SQL’, ‘Server’, ‘Authority’ );

SELECT concat(emp_name ,emp_middlename ,emp_lastname) AS Result
FROM #temp;


The result is


Note : CONCAT will also take care different datatypes and you do not need to explicitely convert the values.


The result is 1TEST


UNPIVOT trick using VALUES clause

Pinal Dave, Blogger and Speaker, posted about SQL SERVER – Creating Dataset Using VALUES Clause Without Creating A Table  Using VALUES clause you can also do what UNPIVOT does in SQL Server

Consider the following data set

create table #t (id int , amount1 decimal(12,2), amount2 decimal(12,2), amount3 decimal(12,2)) insert into #t select 1, 200, 267.40, 188.90
insert into #t select 2, 455.75, 310.25, 4000
insert into #t select 3, 200, 1900, 450
insert into #t select 4, 230.80, 36000, 2100
insert into #t select 5, 1200, 900, 2500.10

If you want to find out the maximum value available in the columns amount1, amount2 and amount3 for each id, you can use UNPIVOT operator

select id,max(amount) as max_amount from #t
unpivot (amount for column_name in (amount1,amount2,amount3)) as t
group by id

The result is

Id      max_amount
----  --------------------
1         267.40
2        4000.00
3        1900.00
4       36000.00
5        2500.00

The same can be done using VALUES clause from version 2008 onwards

select id,
select max(amount) from (values(amount1),(amount2),(amount3)) as t (amount)
) as max_amount
from #t

The result is

Id      max_amount
----  --------------------
1         267.40
2        4000.00
3        1900.00
4       36000.00
5        2500.00