How to find ladder in sql query

[Member 3567411]

Hi, I have table like below col1 5 10 15 20 I need result like Col1 Col2 5 05--9 10 10--14 15 15--19 20 20-24 Plz help Thank You Anil Banjare

[Niral Soni]

How do you define ladder for the last value?? i.e. how do you get 24 in last row?? Apart from that, required query is -

SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2
FROM (
SELECT COL1
,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1
FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5)
)
;

And the output will be -

COL1 COL2
5 5--9
10 10--14
15 15--19
20 NULL

Thanks & Regards, Niral Soni

[vassynet]

SELECT [col1],
CONVERT(VARCHAR(10),[col1])+'--'+ CONVERT(VARCHAR(10),[col1] + 4) AS Col2
FROM Table1

[karna from Hyderabad]

USE tempdb ; GO DECLARE @ladderTable TABLE ( Col1 INT ) ; DECLARE @incrementValue INT = 0 ; INSERT INTO @ladderTable ( Col1 ) VALUES ( 5 ), ( 10 ), ( 15 ), ( 20 ) ; SELECT TOP 1 @incrementValue = Col1 FROM @ladderTable ORDER BY Col1 ASC ; SELECT st.Col1, CAST(st.Col1 AS VARCHAR(20)) + ' - ' + CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence FROM @ladderTable AS st LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1 AND ( st.Col1 + @incrementValue ) >= dt.Col1 ; GO

[karna from Hyderabad]

USE tempdb ; GO DECLARE @ladderTable TABLE ( Col1 INT ) ; DECLARE @incrementValue INT = 0 ; INSERT INTO @ladderTable ( Col1 ) VALUES ( 5 ), ( 10 ), ( 15 ), ( 20 ) ; SELECT TOP 1 @incrementValue = Col1 FROM @ladderTable ORDER BY Col1 ASC ; SELECT st.Col1, CAST(st.Col1 AS VARCHAR(20)) + '-' + CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence FROM @ladderTable AS st LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1 AND ( st.Col1 + @incrementValue ) >= dt.Col1 ; GO Thanks, Karunakar