SQLTeam.com | Weblogs | Forums

Multiple CTE and column display?

tsql

#1

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.

any ideas?


#2

You want [Start] from first row and then [End] from next row (with a defined ordering)? Use LAG and/or LEAD maybe?


#3

Thank you.
So, I am not good with SQL (its been a long long time) so how would I do this? Could you show/demo a sample?

Thank you.


#4

http://www.databasejournal.com/features/mssql/lead-and-lag-functions-in-sql-server-2012.html


#5

Thanks Kristen. For completeness, is there a way to do this pre SQL 2012?


#6

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?


#7

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 :slight_smile:, as per your O/P and just references the "Next/Previous" row, then "yes" :slight_smile:


#8

What you are looking at is a traditional gaps & islands issue...