T- SQL Real Time Scenarios 1

In our daily jobs of SQL, we got some scenarios where we need to build some logic to fulfill specific requirements so I have listed those scenarios here as:

Below data is regarding round trip information’s so we have to show this data into following way:

Source Data:

ID	CityFrom	CityTo
1	Delhi	Noida
2	Faridabad	Kanpur
3	Noida	Delhi
4	Kanpur	Faridabad
5	Banglore	Mumbai
6	G	H
7	I	J
8	Mumbai	Banglore

 Need to generate output in result set:

ID	CityFrom	CityTo
1	Delhi	Noida
3	Noida	Delhi
2	Faridabad	Kanpur
4	Kanpur	Faridabad
5	Banglore	Mumbai
8	Mumbai	Banglore
6	G	H
7	I	J

For example in the first row, we have Delhi to Noida trip and the round trip for this data is available in the third row with ID=1 so we need to show third row’s data just after the first row in the result set and same for others also.

So for this scenario, I have created a sample data by below query:

create table #temp1

 (

     ID Int,

     CityFrom varchar(30),

    CityTo varchar(30)

 )

 insert into #temp1 values (1,'Delhi', 'Noida')

 insert into #temp1 values (2,'Faridabad', 'Kanpur')

 insert into #temp1 values (3,'Noida', 'Delhi')

 insert into #temp1 values (4,'Kanpur', 'Faridabad')

 insert into #temp1 values (5,'Banglore', 'Mumbai')

 insert into #temp1 values (6,'G', 'H')

 insert into #temp1 values (7,'I', 'J')

 insert into #temp1 values (8,'Mumbai', 'Banglore')

 Select * FROM #temp1

And I have written a SQL query by using while loop to generate desired output as:

IF OBJECT_ID('tempdb.dbo.#Temp2', 'U') IS NOT NULL

 Drop table #Temp2

 

 Create table #Temp2

 (    

      ID Int,

     CityFrom varchar(30),

    CityTo varchar(30)

 )

 

--------------------Code to re-arrange the data-------

Declare @i int=1 --> Incremental value to iterate While loop

 

While (@i<=(SELECT max(ID) from #Temp1))

BEGIN

 

      IF NOT EXISTS (SELECT ID, CityFrom,CityTo from #Temp2 where ID=@i)

      BEGIN

           

            INSERT into #Temp2

            SELECT ID, CityFrom,CityTo from #Temp1 where ID=@i

 

            INSERT into #Temp2

           

            SELECT * from #temp1

            where CityTo IN

                        (

                              SELECT CityFrom from #temp1

                              Where Id=@i

                        )

                  AND CityFrom IN

                        (                      

                              SELECT CityTo from #temp1

                              Where Id=@i

                        )

      END        

Set @i=@i+1

END

SELECT * FROM #Temp2

Below is the result of above SQL Query:

ID	CityFrom	CityTo
1	Delhi	Noida
3	Noida	Delhi
2	Faridabad	Kanpur
4	Kanpur	Faridabad
5	Banglore	Mumbai
8	Mumbai	Banglore
6	G	H
7	I	J

NEXT: T- SQL Real Time Scenarios 2

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!