英文:
SQL: Concatenate colums in different lines
问题
SQL：如何将下面的列（a、b、c）连接为单独的行，如果任何字段为空，则不添加空行？所有这三列都具有以下数据类型和示例数据。
a（日期）
b（整数）
c（varchar）
示例数据
a b c
2016-03-17 123 John
2018-05-21 Smith
2023-06-05 451 Park
期望结果：
2016-03-17
123
John
2018-05-21
Smith
2023-06-05
451
Park
我尝试过使用CHAR(13)，但在SQL SSMS（19.0.1）中不起作用。
英文:
SQL: How can I concatenate below columns(a,b,c) as separate lines and if any field is blank then don't add blank line? All the 3 columns have below datatype and sample data.
a (date)
b (int)
c (varchar)
Sample data
a b c
2016-03-17 123 John
2018-05-21 Smith
2023-06-05 451 Park
Expected Result:
2016-03-17
123
John
2018-05-21
Smith
2023-06-05
451
Park
I tried CHAR(13) but it doesn't work in SQL SSMS (19.0.1).
答案1
得分: 1
你可以使用unpivot
;with _list(a,b,c) as (
select
cast( a as varchar(100)) a,
cast( b as varchar(100)) b,
cast( c as varchar(100)) c
from TestB
where a is not null or b is not null or c is not null
)
select value
from _list
unpivot(value for col in (a, b, c) )un
英文:
You Can Use unpivot
;with _list(a,b,c) as (
select
cast( a as varchar(100)) a,
cast( b as varchar(100)) b,
cast( c as varchar(100)) c
from TestB
where a is not null or b is not null or c is not null
)
select value
from _list
unpivot(value for col in (a, b, c) )un
答案2
得分: 0
使用分隔符拼接也许？\n\n 从表中选择 CONCAT_WS (CHAR(13)+CHAR(10),a,b,c)[期望结果]
英文:
Concatenate with separator maybe?
SELECT CONCAT_WS (CHAR(13)+CHAR(10),a,b,c)[EXPECTED RESULT]FROM TABLE
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论