SQL Query Solution

Balaji Palani

10y1.2kSQL

I have one table with rows(1 to 5) and need a output like below

Table:

║no ║Years ║

╠══╬════║

║ 1 ║ 2001 ║

║ 2 ║ 2002 ║

║ 3 ║ 2003 ║

║ 4 ║ 2004 ║

Output :

║no ║Years║

╠══╬════

║ 1 ║ 2001 ║

║ 2 ║ 2002 ║

║ 3 ║ 2003 ║

║ 4 ║ 2004 ║

║ 3 ║ 2003 ║

║ 2 ║ 2002 ║

║ 1 ║ 2001 ║

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmesh Singh10y ago
Accepted answer
Hi balaji palani
I had inserted static value in temp table because I do not have physical table.
You can do with physical table
please consider following code
CREATETABLE#TEMP([NO]INT,[YEAR]INT)
INSERTINTO#TEMP
SELECTT2.NOOFYEAR,T2.[YEAR]ASDEMFROMNOOFYEART2ORDERBYT2.NOOFYEARASC
INSERTINTO#TEMP
SELECTT2.NOOFYEAR,T2.[YEAR]ASDEMFROMNOOFYEART2ORDERBYT2.NOOFYEARDESC
SELECTT1.[NO],T1.[YEAR]FROM#TEMPAST1
in this codeNOOFYEAR is a physical table it work as you want
CREATE TABLE #TEMP ([NO] INT, [YEAR] INT)
INSERT INTO #TEMP
SELECT T2.NOOFYEAR,T2.[YEAR] AS DEM FROM NOOFYEAR T2 ORDER BY T2.NOOFYEAR ASC
INSERT INTO #TEMP
SELECT T2.NOOFYEAR,T2.[YEAR] AS DEM FROM NOOFYEAR T2 ORDER BY T2.NOOFYEAR DESC
SELECT T1.[NO],T1.[YEAR] FROM #TEMP AS T1
Pankaj Kumar Choudhary10y ago
Hello! Balaji try this solution.......
DECLARE @Tab AS TABLE
(
No Int,
Years Int
)
INSERT INTO @Tab
SELECT 1,2001 UNION ALL
SELECT 2,2002 UNION ALL
SELECT 3,2003 UNION ALL
SELECT 4,2004
DECLARE @Temp AS TABLE
(Ser int IDENTITY(1,1),
No Int,
Years Int
);
INSERT INTO @Temp
SELECT t2.No,t2.Years AS dem FROM @Tab t2 ORDER BY t2.No ASC
INSERT INTO @Temp
SELECT t2.No,t2.Years AS dem FROM @Tab t2 ORDER BY t2.No DESC
SELECT t1.No,t1.Years FROM @Temp AS t1
Balaji Palani10y ago
Hi Dharmesh Singh,
Thanks for your ans!. I need a dynamic query I'm not like to insert static((1,'2001'),(2,'2002'),(3,'2003'),(4,'2004')).
Dharmesh Singh10y ago
1. --It'sHelpYou
3. createtable#temp
4. (noint,[Year]int)
5. insertinto#tempvalues(1,'2001'),(2,'2002'),(3,'2003'),(4,'2004')
7. createtable#temp1(idintidentity(1,1),noOfDayint,[Year]int)
10. createtable#temp2(idintidentity(1,1),noOfDayint,[Year]int)
12. insertinto#temp1
13. select*from#temporderby1asc
15. insertinto#temp2
16. select*from#temporderby1desc
19. selectnoOfDay,[YEAR]from#temp1
20. unionall
21. selectnoOfDay,[YEAR]from#temp2
23. droptable#temp
24. droptable#temp1
25. droptable#temp2
it return out put as