Click here to Skip to main content
14,640,678 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello, I have this data:

id    column1
1     Cars
1     Phones
1     Phones : Apple
2     Trucks
2     Phones : Android
3     Trucks
3     Phones

I need to take that data and return this:

id    column1
1     Cars
1     Phones : Apple
2     Trucks
2     Phones : Android
3     Trucks
3     Phones

As you can see, for ID 1 Phones and Phones : Apple get merged together.

What I have tried:

I have googled this a lot and haven't seen anything quite like this done anywhere. The solution needs to work over thousands of rows and hundreds of variations of column1, but the delimiter will always be a colon. Please help!
Posted
Updated 11-May-20 22:31pm
v4

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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 --, column2
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[^]
   
v2
Comments
CPallini 12-May-20 5:16am
   
5.
Maciej Los 12-May-20 5:17am
   
Thank you, Carlo.
MadMyche 12-May-20 8:19am
   
+5
Maciej Los 12-May-20 8:19am
   
Thank you.
nathanjd51 12-May-20 13:40pm
   
Thanks! I added a PARTITION BY id to the OVER statement to keep each id separate and it works great.
Maciej Los 12-May-20 13:47pm
   
Great and you're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100