Generate two columns from single column with alternating data

  • 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!

  • 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!

  • 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

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

  • 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

     

  • 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.

    🙂

  • 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

  • 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/

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

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