I'd suggest to use
LEAD or
LAG functions, like this:
DECLARE @tmp TABLE(id int, column1 varchar(50))
INSERT INTO @tmp(id, column1)
VALUES(1, 'Cars'),
(1, 'Phones'),
(1, 'Phones : Apple'),
(2, 'Trucks'),
(2, 'Phones : Android'),
(3, 'Trucks'),
(3, 'Phones')
SELECT id, column1
FROM
(
SELECT * , LEFT(LEAD(column1) OVER(ORDER BY id), LEN(column1)) AS column2
FROM @tmp
) T
WHERE column1<>column2
Result:
id column1
1 Cars
1 Phones : Apple
2 Trucks
2 Phones : Android
3 Trucks
For further details, please see:
SQL Server Window Functions LEAD and LAG[
^]
SQL Server LAG() Function By Practical Examples[
^]
LEAD (Transact-SQL) - SQL Server | Microsoft Docs[
^]
LAG (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Submit an article or tip