How do I make one column table two columns?
I want to make a one column to two columns table. For example, I have a path table. I have 4 rows, I want to split them into two columns like in the PATH2 table. How can i do this? I want to do this to calculate the value of each path
╔══════╗ ║ PATH ║ ╠══════╣ ║ 1 ║ ║ 2 ║ ║ 3 ║ ║ 4 ║ ╚══════╝
in
╔══════╦═══════╗ ║ PATH ║ PATH2 ║ ╠══════╬═══════╣ ║ 1 ║ 2 ║ ║ 2 ║ 3 ║ ║ 3 ║ 4 ║ ╚══════╩═══════╝
+3
source to share
2 answers
Configuring MS SQL Server 2008 schema :
create table YourTable
(
PATH int
)
insert into YourTable values (1),(2),(3),(4)
Request 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 |
+4
source to share
if you are working with SQL Server 2012
, you can use the analytic function LEAD
.
WITH records
AS
(
SELECT PATH,
LEAD(Path) OVER (ORDER BY PATH) Path2
FROM TableName
)
SELECT Path, Path2
FROM records
WHERE Path2 IS NOT NULL
OR, if on 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
+3
source to share