How to write SP for this SCENARIO?

i have a table like this in database

SlNo
Driver_Id
Sun_StartTime
Sun_EndTime
Mon_StartTime
Mon_EndTime
Tue_StartTime
Tue_EndTime 
Wed_StartTime
Wed_EndTime
Thur_StartTime
Thur_EndTime
Fri_StartTime
Fri_EndTime
Sat_StartTime
Sat_EndTime

the above are the fields in my database

but while r开发者_高级运维etriving i have to retrive like that

Driver_Id | Sun_StartTime  | Sun_EndTime
Driver_Id | Mon_StartTime  | Mon_EndTime
Driver_Id | Tue_StartTime  | Tue_EndTime
Driver_Id | Wed_StartTime  | Wed_EndTime
Driver_Id | Thur_StartTime | Thur_EndTime
Driver_Id | Fri_StartTime  | Fri_EndTime
Driver_Id | Sat_StartTime  | Sat_EndTime

that is i have to split one record into 7 records how can i do this using stored procedure. please help me thanks in advance

Something like this should get you started in the right direction.

SELECT Driver_Id, Sun_StartTime AS StartTime, Sun_EndTime as EndTime, 1 AS DayOfWeek
    FROM YourTable
    WHERE Driver_Id = @YourDriverId
UNION ALL
SELECT Driver_Id, Mon_StartTime AS StartTime, Mon_EndTime as EndTime, 2 AS DayOfWeek
    FROM YourTable
    WHERE Driver_Id = @YourDriverId
UNION ALL
...
SELECT Driver_Id, Sat_StartTime AS StartTime, Sat_EndTime as EndTime, 7 AS DayOfWeek
    FROM YourTable
    WHERE Driver_Id = @YourDriverId
ORDER BY DayOfWeek

check this out: http://wiki.lessthandot.com/index.php/Column_To_Row_%28UNPIVOT%29

You can use Union Query to get this type of results. But you did not mentioned the where clause.

Create Proc testing
AS

Select
driverID, Mon_StartTime,Mon_EndTime From YourTableName

Union

Select
    driverID, Tue_EndTime, Tue_EndTime From YourTableName

Union

Select
    driverID, Wed_StartTime, Wed_EndTime From YourTableName

Union

Select
    driverID, Thur_StartTime, Thur_EndTime From YourTableName

Union

Select
    driverID, Fri_StartTime, Fri_EndTime From YourTableName

Union

Select
    driverID, Sat_StartTime, Sat_EndTime From YourTableName

Union 

    Select
    driverID, Sun_StartTime, Sun_EndTime From YourTableName