Id int identity(1001,1) primary key,

IDVazniZaznam int not null,

DatumCasZacatku datetime,

DatumCasUkonceni datetime

);

(42,'12/04/19 7:45:00 AM'),

(42,'12/04/19 8:00:02 AM')

;

update HVZHlavicka_Prestavka

set DatumCasUkonceni = DatumCasZacatku + 10;

, STRING_AGG(CONCAT(rn,': ',DtStrUkonceni),', ') AS DatumCasUkonceni

, STRING_AGG(CONCAT(rn,': ',DtStrCasZacatku),', ') AS DatumCasZacatku

FROM

(

SELECT DISTINCT IDVazniZaznam

, CONVERT(VARCHAR,DatumCasUkonceni,22) AS DtStrUkonceni

, CONVERT(VARCHAR,DatumCasZacatku, 22) AS DtStrCasZacatku

, ROW_NUMBER() OVER (PARTITION BY IDVazniZaznam ORDER BY DatumCasUkonceni, DatumCasZacatku) AS rn

FROM HVZHlavicka_Prestavka

) q

GROUP BY IDVazniZaznam;

(

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);