我有一个由逗号分隔的"A"列,我想在该列中找到所有唯一的值。
这里是一个非常简短的示例:
Column A
111, 222
333
444
777,999
我需要一个查询语句,用来获取以下数值:
Column C
111
222
333
444
777
999
我有一个由逗号分隔的"A"列,我想在该列中找到所有唯一的值。
这里是一个非常简短的示例:
Column A
111, 222
333
444
777,999
Column C
111
222
333
444
777
999
DECLARE @tmp VarChar(max)
SET @tmp = ''
SELECT @tmp = @tmp + ColumnA + ',' FROM TableA
https://dev59.com/XnRC5IYBdhLWcg3wYP-h#2837662
SELECT DISTINCT * FROM dbo.Split(',', @tmp)
您可以使用知名的Split函数与outer apply
结合使用,将行拆分为多行:
select ltrim(rtrim(s.s)) as colC
from @t t
cross apply
dbo.split(',', t.colA) s
if object_id('dbo.Split') is not null
drop function dbo.Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
go
declare @t table (colA varchar(max))
insert @t select '111, 223'
union all select '333'
union all select '444'
union all select '777,999';
select ltrim(rtrim(s.s)) as colC
from @t t
cross apply
dbo.split(',', t.colA) s