How to insert Serial Number to Unpivoted Column

Question

I have a table in my DB which contains Date and Time separately in columns for a Time Table so for Displaying it as a Single one in the front end I had joined Date and Time Column and Inserted into Temporary table and Unpivoted it,but the Pk_id is same for both the Unpivoted Columns so in the Front end in the Drop down box when I select the item in the Index say at 6 in DDL after a postback occur it will return to Index 1 in DDL.So,is there a way to put Serial number for the Unpivoted columns, My Unpivot Query is,

  Select * from  
(  
  Select pk_bid,No_of_batches,Batch1,Batch2,Batch3,Batch4, from #tempbatch   
) as p  
  Unpivot(Batchname for [Batches] in([Batch1],[Batch2],[Batch3],[Batch4])) as UnPvt  

In the above query pk_bid & No_of_Batches is same so If I put Rownumber() Partition by pk_bid Order by pk_bid or Rownumber() Partition by No_of_Batches Order by No_of_Batches it gives the 1,1 only as it is same.

Answer 1

I had solved My above Problem like this,

I had created another Temporary table and created Serial Number with the column in that table with differant values the Query I had done is,

    Create Table #Tempbatch2
   (
     pk_bid int,  
     No_of_batches int,  
     Batchname Varchar(max),  
     [Batches] Varchar(max)  
   )

    Insert Into #Tempbatch2 
    Select * from  
    (  
      Select pk_batchid,No_of_batches,Batch1,Batch2,Batch3,Batch4 from #tempbatch   
    ) as p  
   Unpivot(Batchname for [Batches] in([Batch1],[Batch2],[Batch3],[Batch4])) as UnPvt

  Select Row_number() OVER(ORDER BY (Batchaname)) as     S_No,pk_bid,No_of_batches,Batchname,[Batches] from #Tempbatch2