如何在单行中显示3列值

Question

我想在SQL中将3列值显示为单行。

输入：

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

输出：

Branch|Phone
abc   |12345,456321,45321

请帮助

Answer 1

我建议您利用CONCAT函数&CONCAT_WS函数，因为它们处理NULL值：如果有NULL值，则用空字符串替换。

有不同的方法可以进行级联

• CONCAT_WS(可从SQL 2017获得(。msdn上的CONCAT_WS

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

• 使用CONCAT(从SQL Server 2012起提供(在msdn上读取CONCAT

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

• 使用串联运算符+的常规串联

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)

• 如果存在NULL的可能性，请使用ISNULL子句来处理带有空字符串的NULL

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)

我强烈推荐CONCAT_WS选项，因为它使代码更简单，并且可以轻松处理NULL。