如何仅根据行号将单个列表转换为多个列?

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?

我有这个数据存在于一个列中。奇数行是ID，偶数行是city。有没有办法把它分成两列?

    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.

噢，对你们这些来自华盛顿的人表示歉意。希望不是新墨西哥。

2 个解决方案

#1

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.

这将与您的小表变量一起工作，但是，如果来自表，则不存在固有的行顺序，并且结果不能是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

#2

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

另一种选择是使用CRLF分隔符将数据解析为字符串，以确保正确的序列

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