How do I turn a single column table into multiple columns based solely on row number?

Question

So, I have this data that exists in a single column. Odd rows are ID, even rows are city. Is there a way to split this into two columns?

    DECLARE @Data TABLE (
          DataRow       NVARCHAR(50)
          )

    INSERT INTO @Data VALUES 

          ('1'              )
        , ('Albuquerque'    )
        , ('2'              )
        , ('Boston'         )
        , ('3'              )
        , ('Chicago'        )
        , ('4'              )
        , ('Dayton'         )
        , ('5'              )
        , ('Eumenclaw'      )
        , ('6'              )
        , ('Fresno'         )

Right now I'm using the following code, but it seems like there should be a more efficient way using a pivot table.

    DECLARE @DataID TABLE (
            ID          INT IDENTITY
          , DataRow     NVARCHAR(50)
          )

    INSERT INTO @DataID
        SELECT * FROM @Data

    DECLARE @CityData TABLE (
          ID            INT
        , City          NVARCHAR(100)
        )

    DECLARE   @Counter      INT = 0
            , @ID           INT
            , @City         NVARCHAR(50)

    WHILE @Counter < (SELECT MAX(ID) / 2 FROM @DataID WHERE ID%2 = 0)
        BEGIN
            SET @Counter += 1
            SET @ID = (SELECT CAST(DataRow AS INT) FROM @DataID WHERE ID = @Counter * 2 - 1)
            SET @City = (SELECT DataRow FROM @DataID WHERE ID = @Counter * 2)
            INSERT INTO @CityData
                SELECT @ID, @City


        END

    SELECT * FROM @CityData

Results:

Oh, and apologies to those of you from Washington for the misspelling. And hopefully not New Mexico.

score 4 · Accepted Answer · answered Jun 06 '17 at 17:15

4

This will work with your small table variable, however, if coming from a table, there is no inherent row order, and results can not be gtd.

Example

Select ID   = max(case when DataRow Like     '[0-9]%' then DataRow end)
      ,City = max(case when DataRow Not Like '[0-9]%' then DataRow end)
 From (
        Select *
              ,Grp = (Row_Number() over (Order by (Select NULL)) -1) / 2
         From @Data
      ) A
 Group By Grp

Returns

ID  City
1   Albuquerque
2   Boston
3   Chicago
4   Dayton
5   Eumenclaw
6   Fresno

answered Jun 06 '17 at 17:15

John Cappelletti

79,615
7
44
66

Wow. I am constantly blown away by the minds and expertise on here. That is really incredible. I've modified it, of course (can't post real data on here, so I use anecdotes). The data I have is entirely predictable, so I'm able to use a MOD function on the ID. But you gave me what I needed, so thanks. Brilliant. – DaveX Jun 06 '17 at 17:53
@DaveX Happy it helped :) – John Cappelletti Jun 06 '17 at 17:54
@DaveX I too love SO. I learn something new every day. That's the fun part – John Cappelletti Jun 06 '17 at 17:55

score 0 · Answer 2 · answered Jun 06 '17 at 17:38

Yet another option, to ensure the proper sequence is to parse data as string with a CRLF delimiter

Consider the following:

Declare @Delimiter varchar(25) = char(13)+char(10)
Declare @String varchar(max) = '
1
Albuquerque
2
Boston
3
Chicago
4
Dayton
5
Eumenclaw
6
Fresno
'

Select ID   = max(case when RetSeq % 2 = 1 then RetVal end)
      ,City = max(case when RetSeq % 2 = 0 then RetVal end)
 From (
        Select *,Grp = (RetSeq-1) / 2
         From (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
                Where LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)'))) is not null
              ) A1
      ) A
 Group By Grp

Returns

ID  City
1   Albuquerque
2   Boston
3   Chicago
4   Dayton
5   Eumenclaw
6   Fresno

How do I turn a single column table into multiple columns based solely on row number?

2 Answers2