带有数字的 SQL 前缀

Question

在我的select中，我在一行中有多个日期，用逗号分隔。

主要选择：

SELECT DISTINCT p.IDVazniZaznam,
stuff(
(
SELECT ','+ CONVERT(VARCHAR,DatumCasZacatku, 22) FROM HVZHlavicka_Prestavka WHERE IDVazniZaznam = p.IDVazniZaznam FOR XML PATH('')
),1,1,'') As DatumCasZacatku,
stuff(
(
SELECT ','+ CONVERT(VARCHAR,DatumCasUkonceni, 22) FROM HVZHlavicka_Prestavka WHERE IDVazniZaznam = p.IDVazniZaznam FOR XML PATH('')
),1,1,'') AS DatumCasUkonceni
FROM (SELECT DISTINCT IDVazniZaznam, DatumCasUkonceni, DatumCasZacatku FROM HVZHlavicka_Prestavka ) p

喜欢这个：

12/04/19  7:45:00 AM,12/04/19  8:00:02 AM

我需要做的是这样的：

1: 12/04/19  7:45:00 AM, 2: 12/04/19  8:00:02 AM

我不确定它是否称为前缀，但我认为是。我不想手动放置它，但我想自动生成它。第一个日期将是数字 1，第二个日期将是数字 2，依此类推。 这在SQL甚至可能吗？

Answer 1

您可以使用以下代码中的逻辑-

DECLARE @text VARCHAR(MAX) = '12/04/19  7:45:00 AM,12/04/19  8:00:02 AM'
DECLARE @remaining_text VARCHAR(MAX) = @text
DECLARE @new_text VARCHAR(MAX) = ''
DECLARE @CommaIndex INT
DECLARE @Count INT = 1
SET @CommaIndex = CHARINDEX(',',@remaining_text,0)
IF @CommaIndex = 0
BEGIN
SET @new_text = '1: ' + @text
END
ELSE
BEGIN
WHILE @CommaIndex > 0
BEGIN
SET @new_text = @new_text+ ' '+CAST(@Count AS VARCHAR)+': ' + LEFT(@remaining_text,CHARINDEX(',',@remaining_text,0))
SET @remaining_text = RIGHT(@remaining_text,LEN(@remaining_text)-CHARINDEX(',',@remaining_text,0))
SET @CommaIndex = CHARINDEX(',',@remaining_text,0)
SET @Count = @Count + 1
END
END

IF CHARINDEX(',',@text,0) > 0 
BEGIN
SET @new_text = @new_text + ' '+CAST(@Count AS VARCHAR)+': ' + @remaining_text
END
SELECT  @new_text,@remaining_text

输出是-

1: 12/04/19  7:45:00 AM, 2: 12/04/19  8:00:02 AM

Answer 2

你可以同时使用STRING_AGG()、ROW_NUMBER() OVER (ORDER BY ..)、STRING_SPLIT()和CONCAT()函数(STRING_AGG()适用于SQL Server 2017+(。

在第一步中，我们通过子查询中的逗号分隔符拆分字符串，然后通过使用STRING_AGG()连接这些子字符串：

DECLARE @DatumCasUkonceni NVARCHAR(1000) = '12/04/19  7:45:00 AM,12/04/19  8:00:02 AM';
SELECT STRING_AGG(q.str, ',' ) as "Result"
FROM
(
SELECT CONCAT( ROW_NUMBER() OVER (ORDER BY @DatumCasUkonceni), ' : ',
value ) as str
FROM STRING_SPLIT(@DatumCasUkonceni, ',')  
) q  

演示

Answer 3

如果STRING_AGG可用于您的MS SQL Server版本(2017+(，那么这样的东西应该可以完成这项工作。

SELECT IDVazniZaznam
, STRING_AGG(CONCAT(rn,': ',DtStrCasZacatku),', ') AS DatumCasZacatku
, STRING_AGG(CONCAT(rn,': ',DtStrUkonceni),', ') AS DatumCasUkonceni
FROM 
(
SELECT DISTINCT IDVazniZaznam
, CONVERT(VARCHAR,DatumCasZacatku, 22) AS DtStrCasZacatku
, CONVERT(VARCHAR,DatumCasUkonceni,22) AS DtStrUkonceni
, ROW_NUMBER() OVER (PARTITION BY IDVazniZaznam ORDER BY DatumCasZacatku, DatumCasUkonceni) AS rn
FROM HVZHlavicka_Prestavka
) q
GROUP BY IDVazniZaznam;

这也适用于MS Sql Server 2012

WITH CTE AS
(
SELECT DISTINCT IDVazniZaznam
, DatumCasZacatku
, DatumCasUkonceni
, ROW_NUMBER() OVER (PARTITION BY IDVazniZaznam ORDER BY DatumCasUkonceni, DatumCasZacatku) AS rn
FROM HVZHlavicka_Prestavka
)
SELECT q.IDVazniZaznam
, STUFF(a1.DatumCasZacatku,1,2,'') AS DatumCasZacatku
, STUFF(a2.DatumCasUkonceni,1,2,'') AS DatumCasUkonceni
FROM 
(
SELECT IDVazniZaznam
FROM CTE
GROUP BY IDVazniZaznam
) AS q
OUTER APPLY
(
SELECT CONCAT(', ',rn,': ', CONVERT(VARCHAR,DatumCasZacatku, 22))
FROM CTE c
WHERE c.IDVazniZaznam = q.IDVazniZaznam
FOR XML PATH ('')
) a1(DatumCasZacatku)
OUTER APPLY
(
SELECT CONCAT(', ',rn,': ', CONVERT(VARCHAR,DatumCasUkonceni,22))
FROM CTE c
WHERE c.IDVazniZaznam = q.IDVazniZaznam
FOR XML PATH ('')
) a2(DatumCasUkonceni);

在这里测试数据库<>小提琴