问题:
I have the below code that is returning the order that zip codes are visited. I am able to return the zip codes correctly, but in order to make the data more user friendly...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have the below code that is returning the order that zip codes are visited. I am able to return the zip codes correctly, but in order to make the data more user friendly I have added a dash(-) between the zip codes.
The problem comes from the fact that I cannot figure out how to eliminate the dashes for rows that only have 2 or 3 zip codes.
SELECT
[Qry_Zip Stop Sequence].[Load ID],
[1] AS [Stop 1], [2] AS [Stop 2], [3] AS [Stop 3],
[4] AS [Stop 4],
TMS_Load.[Shipped Date/Time],
CONCAT(ISNULL([1], ''), '-', ISNULL([2], ''), '-', ISNULL([3], ''), '-', ISNULL([4], '')) AS [Zip to Zip w Stops]
FROM
(SELECT
[Load ID], [Sequence], [Stop Zip]
FROM
TMS_Load_Stops) ls
PIVOT
(MIN([Stop Zip])
FOR [Sequence] IN ([1], [2], [3], [4])) AS [Qry_Zip Stop Sequence]
INNER JOIN
[TMS_Load] ON [TMS_Load].[Load ID] = [Qry_Zip Stop Sequence].[Load ID];
I would like the results to show only show the dashes between valid zip codes.
78052-45050-45201 or
73350-45220 or
84009-48009-14452 or
36521-38222-87745-95123 or
73368 or
12789-35789
回答1:
Prepend every value with a dash and remove the first dash in the resulting string -- that isn't necessarily the one from the first value -- using stuff()
.
stuff(concat('-' + [1],
'-' + [2],
'-' + [3],
'-' + [4]),
1,
1,
'')
Note: I deliberately mixed +
and concat()
for string concatenation here. +
results in NULL
when a value is NULL
but concat()
treats NULL
s like empty strings. That way we don't need to use a lot of coalesce()
s or isnull()
s etc..
回答2:
SQL Server 2017 supports CONCAT_WS
which is designed for such scenario:
CONCAT_WS ignores null values during concatenation, and does not add the separator between null values. Therefore, CONCAT_WS can cleanly handle concatenation of strings that might have "blank" values - for example, a second address field
SELECT *, CONCAT_WS('-', Stop1, Stop2, Stop3, Stop4) AS r
FROM tab
db<>fiddle demo
回答3:
The following code will insert separators only for non-NULL values. It is assumed that columns are populated from left to right.
declare @Stops as Table ( Stop1 Char(5), Stop2 Char(5), Stop3 Char(5), Stop4 Char(5) );
insert into @Stops ( Stop1, Stop2, Stop3, Stop4 ) values
( '00001', null, null, null ),
( '00001', '00002', null, null ),
( '00001', '00002', '00003', null ),
( '00001', '00002', '00003', '00004' );
select Coalesce( Stop1, '' ) + Coalesce( '>' + Stop2, '' ) + Coalesce( '>' + Stop3, '' ) +
Coalesce( '>' + Stop4, '' )
from @Stops;
Aside: A separator other than dash may be less confusing in lands where ZIP+4 codes are used.