Merge 2 rows into one

Transact-SQL
1

This involves data exported from SharePoint. SP exports a people list into a single column:

Fred Smyth;#6169;#Jane Jones;#6166;#Dan Dennis;#1451

I have code to convert it to multiple rows like this:

Fred Smyth
6169
Jane Jones
6166
Dan Dennis
1451

But I would like to convert it to one row, 2 columns

Fred Smyth | 6169
Jane Jones | 6166
Dan Dennis | 1451

Any help is appreciated

2

Once you split the data in put it into a table, there is no way to associate a name with a number unless you have another column to help you do that. This is because the data in a table is an unordered collection. It would be better to do the splitting and making it into two rows in one shot.

Using the string splitter function DelimitedSplit8K described here you can get the results you want like shown below

DECLARE @s VARCHAR(8000) = 'Fred Smyth;#6169;#Jane Jones;#6166;#Dan Dennis;#1451';
;WITH cte AS
(
	SELECT * FROM master.dbo.delimitedsplit8k(REPLACE(@s,';',''),'#')
)
SELECT
	a.Item,
	b.Item
FROM 
	cte a
	LEFT JOIN cte b ON
		a.ItemNumber+1 = b.ItemNumber
WHERE
	a.ItemNumber % 2 = 1
3

declare @t as table
(
sno int identity(1,1),
val varchar(20)
)
insert into @t
select 'Fred Smyth'
insert into @t
select '6169'
insert into @t
select 'Jane Jones'
insert into @t
select '6166'
insert into @t
select 'Dan Dennis'
insert into @t
select '1451'

select a.val, b.val
from @t a
inner join @t b
on a.sno+1 = b.sno
where b.sno%2=0