RE: Generate two columns from single column with alternating data

SSC Guru

Points: 323506

April 29, 2013 at 1:56 pm

you can join the table against itself, and use the modulus operator to limit it to the "odd" rows for the first value, leaving the second value as the date-ish type field:

/*

ValVal

John Smith9/13/1961

Phony Persson2/24/1943

Doc Galacawicz11/11/1999

*/

With MyInputdata (ID,Val)

AS

(

SELECT 1,'John Smith' UNION ALL

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

SELECT 3,'Phony Persson' UNION ALL

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

SELECT 5,'Doc Galacawicz' UNION ALL

SELECT 6,'11/11/1999'

)

SELECT T1.Val, T2.Val

FROM MyInputdata T1

INNER JOIN MyInputdata T2 ON T1.ID + 1 = T2.ID

WHERE T1.ID %2 = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!