如何使一列表两列?

I wish to make a table from one column to two columns. For example I have a path table.I have 4 rows, I wish to split it to two columns like in the PATH2 table.How may I do that? I wish to do this in order to calculate each paths value

我希望从一列到两列做一个表。例如，我有一个路径表。我有4行，我想把它分成两列，就像在PATH2表中那样。我该怎么做呢?我希望这样做是为了计算每个路径的值

╔══════╗
║ PATH ║
╠══════╣
║    1 ║
║    2 ║
║    3 ║
║    4 ║
╚══════╝

into

成

╔══════╦═══════╗
║ PATH ║ PATH2 ║
╠══════╬═══════╣
║    1 ║     2 ║
║    2 ║     3 ║
║    3 ║     4 ║
╚══════╩═══════╝

2 个解决方案

#1

SQL Fiddle

SQL小提琴

MS SQL Server 2008 Schema Setup:

MS SQL Server 2008模式设置:

create table YourTable
(
  PATH int
)

insert into YourTable values (1),(2),(3),(4)

Query 1:

查询1:

select T1.PATH,
       Lead.PATH as PATH2
from YourTable as T1
  cross apply (
              select top(1) PATH
              from YourTable as T2
              where T2.PATH > T1.PATH
              order by T2.PATH
              ) as Lead

Results:

结果:

| PATH | PATH2 |
----------------
|    1 |     2 |
|    2 |     3 |
|    3 |     4 |

#2

if you are working on SQL Server 2012, you can use LEAD analytic function.

如果您正在开发SQL Server 2012，您可以使用LEAD analytic function。

WITH records
AS
(
    SELECT  PATH,
            LEAD(Path) OVER (ORDER BY PATH) Path2
    FROM    TableName
)
SELECT  Path, Path2
FROM    records 
WHERE   Path2 IS NOT NULL

• SQLFiddle Demo
• SQLFiddle演示

OR if on SQL SERVER 2005+

或者如果在SQL SERVER 2005+上。

WITH records
AS
(
    SELECT  PATH,
            ROW_NUMBER() OVER (ORDER BY PATH) rn
    FROM    TableName
)
SELECT  a.Path, b.Path AS Path2
FROM    records a
        INNER JOIN records b
          ON a.rn+1 = b.rn

• SQLFiddle Demo
• SQLFiddle演示