Uses
RegexSplit.
Given the following:
select pk = p.[0],
v = p.[1]
from dbo.RegexSplit(
'1 one
2 two
3 three
4 four
5 five'
, '\r\n') lines
cross apply dbo.RegexSplit(value, '\t') cols
pivot (max(cols.value) for cols.idx in ([0], [1])) p
The following table is returned:
pk v
-- -----
1 one
2 two
3 three
4 four
5 five
To make the below a bit cleaner (allow leading and trailing newlines) and allow for column headers:
select pk = p.[0],
v = p.[1]
from dbo.RegexSplit(dbo.RegexReplace('
!pk v
1 one
2 two
3 three
4 four
5 five
'
, '(?m:^(!.*)?\r\n)|(?-m:\s+$)', ''), '\r\n') lines
cross apply dbo.RegexSplit(value, '\t') cols
pivot (max(cols.value) for cols.idx in ([0], [1])) p