Currently this is what I have and I would want to parse out the userid and add it to the line below it (and before the next line that contains another userid) as a new field.
The result would look like this. Any idea how this would be accomplished in SQl Server?
Advertisement
Answer
Hmmm . . . One method is to use a conditional window function to get the preceding row with '##User: '
. Then join back to that row to get the user:
x
select t.*,
stuff(tu.notes, 1, 8, '') as user
from (select t.*,
max(case when notes like '##User: %' then lineno end) over (partition by orderno order by lineno) as user_lineno
from t
) t left join
t tu
on tu.lineno = t.user_lineno
where notes not like '##User: %';