10/07/2015

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

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

Thanks!