Link to home
Start Free TrialLog in
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".
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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
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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of holemania
holemania

ASKER

Awesome.  Thank you so much!!!