Merge consecutive rows into single row

Question

Post reply

Merge consecutive rows into single row

waxb18

Ten Centuries

Points: 1060
More actions
January 5, 2017 at 6:22 am

#316591

Afternoon Ladies and Gents,
I'm having some frustrations in what seems to be a simple issue but is causing me problems.
We have received a set of data embedded in an HTML table within an email. The issue with this is that within the cells we have carriage returns, which SQL is reading as individual columns.
As an example names are appearing as below
And the required result
Create Table #Table
(ID int identity(1,1) not null,
String nvarchar(50),)
Insert into #Table
Values
('Dave'),
('Smith'),
(''),
('Michael'),
('Edwards'),
(''),
('Darren'),
('Michael'),
('Taylor'),
(''),
('Katie'),
('Von'),
('Barker'),
('Smith'),
('')
Select * FROM #Table
I've tried using a lag/lead functions to define if the next row is blank, however i can only seem to get this working for instances where there are only 2 consecutive rows of data as per below (i know its not pretty)
Select
ID,
String,
Case when ID = 1 then 2 else ID - LAG(ID,1,0) over (order by ID) END DeleteFlag,
Case when
Case
When ID -LEAD(ID,1,0) over (order by ID) = -1
then CONCAT(String, ' ',LEAD(String,1,0) over (order by ID))
END IS null
Then NULL
Else
Case
When ID -LEAD(ID,1,0) over (order by ID) = -1
then CONCAT(String, ' ',LEAD(String,1,0) over (order by ID))
END
END Dupe
FROM #Table
where String <> ''
Your help would be much appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 1

Why not just replace the embedded carriage returns with an empty string?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99130 More actions · Answer 2

Jeff Moden (1/5/2017)
Why not just replace the embedded carriage returns with an empty string?

Would that not just provide one continuous string instead?

I'm sure sure this is particularly pretty, and it doesn't give the the blank row with an ID of 8, but:

WITH Names AS (

SELECT T.ID,

T.String,

LAG(T.String, 1, '') OVER (ORDER BY T.ID) AS PrevLine,

EL.EndLine

FROM #Table T

CROSS APPLY (SELECT TOP 1 ID AS EndLine

FROM #Table sq

WHERE sq.ID > T.ID

AND sq.String = '') EL)

SELECT ROW_NUMBER() OVER (ORDER BY N.ID) AS ID,

CASE WHEN N.String = '' THEN ''

ELSE STUFF((SELECT ' ' + sq.String

FROM #Table sq

WHERE sq.ID BETWEEN N.ID AND N.EndLine -1

ORDER BY sq.ID

FOR XML PATH ('')),

1, 1, '')

END AS String

FROM Names N

WHERE N.PrevLine = ''

OR N.String = '';

GO

Edit' Alignment fixing

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Jeff Moden (1/5/2017)
Why not just replace the embedded carriage returns with an empty string?

This gets my vote. Fix the data before or during load into SQL Server.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service