Below is a solution that formats your data in one query. Performance is not an issue with a small data set of 4 rows (I added a row to your example), but my guess is that this will not be fast for millions of records.
The solution provided here generates different data sets in the form of common table expressions (CTE) and uses some techniques from other StackOverflow answers to remove the seconds and concatenate the row values. Plus a cross apply at the end.
The approach can be described in steps that correspond with the consecutive CTE's / joins:
- Create a set of attributes for each product.
- Create a set of period start moments for each product (leaving out the seconds).
- Combine the attributes for each product with each period and look for the appropriate value.
- Use some XML functions to format the attributes values in a single row.
- Use
cross apply to fetch the period end.
Full solution:
-- sample data
declare @data table
(
ID int,
ProductId int,
Attribute nvarchar(10),
Value nvarchar(10),
SysStartTime datetime2(0),
SysEndTime datetime2(0)
);
insert into @data (ID, ProductId, Attribute, Value, SysStartTime, SysEndTime) values
(1, 8, 'Size', 'S', '2020-07-06 05:00:00', '9999-12-31 23:59:59'),
(2, 8, 'Color', 'Blue', '2020-07-06 05:00:01', '2020-07-09 11:11:11'),
(2, 8, 'Color', 'Green', '2020-07-09 11:11:11', '9999-12-31 23:59:59'),
(2, 8, 'Weight', 'Light', '2020-07-10 10:11:12', '9999-12-31 23:59:59'); -- additional data to have extra attribute not available from start
-- solution
with prodAttrib as -- attributes per product
(
select d.ProductId, d.Attribute
from @data d
group by d.ProductId, d.Attribute
),
prodPeriod as -- periods per product
(
select d.ProductId,
dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) as 'SysStartTimeNS' -- start time No Seconds
from @data d
group by ProductId, dateadd(minute, datediff(minute, 0, d.SysStartTime), 0)
),
prodResult as -- attribute value per period per product
(
select pp.ProductId,
convert(nvarchar(16), pp.SysStartTimeNS, 120) as 'FromDateTime',
convert(nvarchar(16), coalesce(pe.SysEndTime, '9999-12-31 23:59:59'), 120) as 'ToDateTime',
pa.Attribute,
av.Value
from prodPeriod pp
join prodAttrib pa
on pa.ProductId = pp.ProductId
outer apply ( select top 1 d.Value
from @data d
where d.ProductId = pp.ProductId
and d.Attribute = pa.Attribute
and dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) <= pp.SysStartTimeNS
order by d.SysStartTime desc ) av -- attribute values per product
outer apply ( select top 1 dateadd(second, -1, d.SysStartTime) as 'SysEndTime'
from @data d
where d.ProductId = pp.ProductId
and dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) > pp.SysStartTimeNS
order by d.SysStartTime ) pe -- period end
),
prodResultFormat as -- concatenate attribute values per period
(
select pp.ProductId,
convert(nvarchar(16), pp.SysStartTimeNS, 120) as 'FromDateTime',
(
select pr.Attribute + ' = ' + coalesce(pr.Value,'') + ', ' as [text()]
from prodResult pr
where pr.ProductId = pp.ProductId
and pr.FromDateTime = convert(nvarchar(16), pp.SysStartTimeNS, 120)
order by pr.Attribute
for xml path('')
) as 'Attributes'
from prodPeriod pp
)
select prf.ProductId,
prf.FromDateTime,
x.ToDateTime,
left(prf.Attributes, len(prf.Attributes)-1) as 'Attributes'
from prodResultFormat prf
cross apply ( select top 1 pr.ToDateTime
from prodResult pr
where pr.ProductId = prf.ProductId
and pr.FromDateTime = prf.FromDateTime ) x
order by prf.ProductId, prf.FromDateTime;
Result for extended example data:
ProductId FromDateTime ToDateTime Attributes
----------- ---------------- ---------------- ----------------------------------------
8 2020-07-06 05:00 2020-07-09 11:11 Color = Blue, Size = S, Weight =
8 2020-07-09 11:11 2020-07-10 10:11 Color = Green, Size = S, Weight =
8 2020-07-10 10:11 9999-12-31 23:59 Color = Green, Size = S, Weight = Light
P.S. replace x.EndDateTime with case when x.ToDateTime = '9999-12-31 23:59' then NULL else x.ToDateTime end as 'ToDateTime' if you really need the NULL values.
