So, I am playing with some SQL Code.
Let's say we have a SQL Table (Start (int), End (int)) with the following data:
1, 2
3, 4
6, 10
11, 13
I want to be able to get the following result:
1, 4
6, 13
I am sure there are better ways of doing this but this is what I can think of and it almost works:
WITH x AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Start ASC) AS StartRowNumber, Start
FROM Numbers
)
SELECT *
FROM x
WHERE StartRowNumber % 2 = 1
ORDER BY Start;
WITH x AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [End] ASC) AS RowNumber, [End]
FROM Numbers
)
SELECT *
FROM x
WHERE RowNumber % 2 = 0
ORDER BY [End];
What I want to do now is some how merge the rows in that the results from the 2nd CTE are put into the columns into the first CTE or write some query that merges the results together displaying x and then x2 next to each other.
Hmm, thinking about it - I don't think this will quite work for me.
I have 2 CTE's, both displaying the correct results. I now want to create 1 result set where the results of CTE 2 is displayed in a new column next to the results of CTE 1. Possible?
Its harder work. Probably do-able with CTE and ROW_NUMBER(), but before that I would have put the results of query into #TEMP, including an IDENTITY column, and then self-joined
FROM MainTable AS M
LEFT OUTER JOIN #TEMP AS T
on T.ID = M.ID+1
to have the next row available to the SELECT
If you can generate output as per your original query:
1, 2
3, 4
6, 10
11, 13
and what you want is, again , as per your O/P and just references the "Next/Previous" row, then "yes"
, as per your O/P and just references the "Next/Previous" row, then "yes"