How to display 3 column value in a single rows

Question

I want to display 3 column values into single rows in SQL.

Input:

Branch |Phone1|Phone2|Phone3
abc    |12345 |456321|456321

Output:

Branch|Phone
abc   |12345,456321,45321

Please help

Answer 1

I would suggest you to leverage CONCAT function & CONCAT_WS function, as they handle NULL values: If there is a NULL value, it is replaced with empty string.

Different approaches are available to do concatenation

• CONCAT_WS (Available from SQL 2017). CONCAT_WS on msdn

SELECT Branch, concat_ws(',',phone1, phone2, phone3) as phone
FROM
(
VALUES
('abc',12345,456321,456321)
) AS T(Branch,Phone1,Phone2,Phone3)

• Using CONCAT (Available from SQL Server 2012 onwards) Read CONCAT on msdn

SELECT Branch, concat(phone1,',', phone2,',', phone3) as phone
FROM
(
VALUES
('abc',12345,456321,456321)
) AS T(Branch,Phone1,Phone2,Phone3)

• Regular concatenation using concatenation operator +

SELECT Branch, cast(phone1 as varchar(10)) + ',' +  cast(phone2 as varchar(10)) + ',' +  cast(phone3 as varchar(10)) as phone
FROM
(
VALUES
('abc',12345,456321,456321)
) AS T(Branch,Phone1,Phone2,Phone3)

• If there is a possibility of NULL, have ISNULL clause to handle NULLs with empty string.

SELECT Branch, cast(ISNULL(phone1,'') as varchar(10)) + ',' +  cast(phone2 as varchar(10)) + ',' +  cast(phone3 as varchar(10)) as phone
FROM
(
VALUES
('abc',null,456321,456321)
) AS T(Branch,Phone1,Phone2,Phone3)

I would highly recommend CONCAT_WS option, as it makes the code simpler and handles NULLs easily.