Assuming that the PKs would always be in ODD/EVEN order would make me nervous. What if data has been inserted or deleted and the IDs are no longer sequential? What if one of the pairs is missing or out of order?

The code below isn't bulletproof, but it will at least pair the values correctly even if values are missing or the keys are not in sequential order by creating pseudo-keys. Otherwise, it's the same algorithm presented by Lowell.

USE LocalTestDB

GO

WITH MyInputdata (ID,Val)

AS

(

SELECT 1,'John Smith' UNION ALL

SELECT 2,'9/13/1961' UNION ALL

SELECT 4,'Phony Persson' UNION ALL

SELECT 5,'2/24/1943' UNION ALL

SELECT 15,'Doc Galacawicz' UNION ALL

SELECT 19,'11/11/1999' UNION ALL

SELECT 21,'Mary Jones' UNION ALL

SELECT 22,'' UNION ALL

SELECT 43,'3/22/1953' UNION ALL

SELECT 52,'Danny Jones' UNION ALL

SELECT 67,'John Paul' UNION ALL

SELECT 66,'12/18/1987'

)

SELECT

Name

,BDate

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY ID) AS rn1

,ID

,NULLIF(Val,'') AS Name

FROM

MyInputdata

WHERE

ISDATE(Val) = 0

AND NULLIF(Val,'') IS NOT NULL

) a

LEFT OUTER JOIN

(

SELECT

ROW_NUMBER() OVER (ORDER BY ID) AS rn2

,ID

,NULLIF(Val,'') AS BDate

FROM

MyInputdata

WHERE

ISDATE(Val) = 1

OR NULLIF(Val,'') IS NULL

) b

ON a.rn1 = b.rn2