Generate two columns from single column with alternating data

Question

Generate two columns from single column with alternating data

michaellascuola

SSC Enthusiast

Points: 134
More actions
April 29, 2013 at 1:50 pm

#277206

I have what I thought would be a simple PIVOT example, but I have not yet solved it without using a temp table.
My input data looks like this:
1John Smith
29/13/1961
3Phony Persson
42/24/1943
5Doc Galacawicz
611/11/1999
……
And I want the SQL output to look like this:
NameDate
John Smith9/13/1961
Phony Persson2/24/1943
Doc Galacawicz11/11/1999
……
Any thoughts?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Another option:

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

CROSS APPLY (

SELECT Val

FROM MyInputdata

WHERE ID % 2 = 0

AND ID = T1.ID + 1

) AS T2 (Val);

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

michaellascuola SSC Enthusiast Points: 134 More actions · Answer 3

Great - these both work well for me. Thanks for the fast replies!

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

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

michaellascuola SSC Enthusiast Points: 134 More actions · Answer 5

Thanks -- I always love robust code.

In this case, it's a (relatively) small data set, and the pattern never deviates.

(TMI)

The table is populated from the output of a powershell script that checks the password expiration date of SQL Server service accounts. So, the source data initially looks like this:

[font="Courier New"]...

cn : MSSQL_INSTANCE01_SVC

...

PasswordExpires : 3/20/2014 1:09:02 PM

...[/font]

... my apologies, I'm losing some formatting here even though I changed to a mono-spaced font.

Next, I strip out the "cn:" and "PasswordExpires:" and am left with just the alternating data.

🙂

DAVNovak Hall of Fame Points: 3120 More actions · Answer 6

Provided the date rows are always valid dates and follow a row with a name, there is this solution:

create table #test (Id int, Value varchar(25))

insert into #test

select 1, 'John Smith'

union select 2, '9/13/1961'

union select 3, 'Phony Persson'

union select 4, '2/24/1943'

union select 5, 'Doc Galacawicz'

union select 6, '11/11/1999'

SELECT a.Value, b.Value

FROM #test a INNER JOIN #test b on a.Id = b.Id - 1

AND ISDATE(b.Value) = 1

WHERE ISDATE(a.Value) = 0

ORDER BY a.Id

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

So, using this sample data: -

SELECT ID, Val

INTO MyInputdata

FROM (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')a(ID, Val);

Anything wrong with just doing this: -

SELECT

MAX(CASE WHEN pos % 2 = 1 THEN Val ELSE NULL END),

MAX(CASE WHEN pos % 2 = 0 THEN Val ELSE NULL END)

FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID), Val

FROM MyInputdata

)a(pos,Val)

GROUP BY (pos + 1) / 2;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/