Avatar of holemania
holemania
 asked on

SQL Query - Combine multiple rows into 1 base off keywords

I need help with a query.  I am reading a log file from a text file into a temp table.  Got this portion working, but I need help with combine multiple rows into previous row if the following rows don't have this keyword in there.

Example Raw data line by line (also row by row in table):
Line 1
Line 2
Line 3
Line 4
Line 5
Failed:  No item in db
Please verify item exists
Line 6
Line 7
Error:  Not able to verify job
Line 8

Output need in:
Line 1
Line 2
Line 3
Line 4
Line 5 Failed:  No item in db Please verify item exists
Line 6
Line 7 Error:  Not able to verify job
Line 8

Is it possible to do that?  Basically look for keyword Line since that does not exists any where else except for the Line #.  If no keyword "Line", then combine any rows with previous record that has "Line".
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
holemania

8/22/2022 - Mon
Kyle Abrahams

select 'Line 1' data, 1 id into #temp union
select 'Line 2', 2  union
select 'Line 3', 3 union
select 'Line 4', 4 union
select 'Line 5', 5 union
select 'Failed: No item in db', 6  union
select 'Please verify item exists' , 7 union
select 'Line 7', 8  union
select 'Error: Not able to verify job', 9 union
select 'Line 8', 10


 
 alter table #temp
 add next_line int

 update t
 set next_line =
 (select MIN(id) from #temp t2 where id > t.id and t2.data like 'line%')
 from #temp t
 
/*
select * from #temp
order by id
*/

select id,
(STUFF(
(
SELECT ' ' + it.data + ' ' + isnull(it2.data, ' ')
FROM #temp it
 left join #temp it2 on it.next_line = it2.next_line and it2.id > it.id
 where t.id = it.id
 ORDER BY it.ID FOR XML PATH('')), 1,1,'') ) test
 from #temp t
 where t.data like 'line%'
order by id
Kyle Abrahams

The above assumes you have the lines numbered in some way.  If not when you insert it into your table or whatever ensure you have a sequential order as there's no way to determine what order they should be in otherwise.
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
holemania

ASKER
Awesome.  Thank you so much!!!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy