This is a good way to solve this problem but, it is not giving proper output in all the cases. Execute both the queries with the test data provided above...

Ok, if your requirement is to get data as mentioned above then try below query.

DECLARE @tbl TABLE (Id int identity(1, 1), Name Varchar(100), Date Datetime, Trend varchar(10), Strength varchar(10))

insert into...

Check if below helps you, you can make use of CTE too, but in order to keep it simple I have used an inline query.

DECLARE @tbl TABLE (Name Varchar(10), Date...