sql - Comparing partitions of a table and creating index for unique partition in TSQL (if it's possible) -

I have to do a simple job though it can be impossible to use TSQL.

I want to compare the sequences of the SQL table on the sequence of elements (for example, simply stops the order of the route). Here's my table:

  id bus ner sick stop name dated 1 78 9 1a 2015-04-08 2 78 9 2b 2015-04-08 3 78 9 3c 2015-04- 08 4 78 9 4D 2015-04-08 5 789 1A 2015-04-16 6 78 9 2B 2015-04-16 7 789 3D 2015-04-16 8 789 1A 2015-05-10 9 789 2b 2015-05- 10 10 78 9 3C 2015-05-10 11 78 9 4D 2015-05-10 12 78 9 5 E 2015-05-10  

Sample Shows a bus line that takes different routes, three different dates are given by road stop sequences. I need to find a way to stop the sequence of passages and find a way to find unique bus stop lines. Then I would like to create a unique stop sequence (with index for each one), which I can connect to the table above.

Is it possible to connect on the basis of the identities of such sequences?

Please kindly give me some advice to solve my problem. I am thinking that I can use window work, but at the moment I could not find the solution. [Very few reps to comment] I want to help, but your purpose is not clear. I get this: A route is a unique list of stops. There are different routes on different days. I need a special route for billline.

  Maybe you want to: 1. 789 ABC, 2. 789 ABD, 3. 789 ABCDE  

You need anything else , When it is to join the 3 possible matches on the busline to make it unique, to tie it with the date ... you can count as a couple of weeks, two weeks, ... do you agree?

, if you need to use 1 line per route, or you want to decide it in split then:

  P INT, BusNr INT , RouteStopSeq INT, RouteStopName CHAR, RouteWeekNumber INT 1, 789, 1, A, 2, 789, 2, B, 1 to 3, 789, 3, C, 1 to 4, 789, 1, A, 2 to 5, 789, 2, b, 2 to 6, 789, 3, d, 2 ...  

So ... each day there is a separate route that table is made Makes the same data as you provided ... the overall pattern from such a small set.

Insert in the tempdb of the CREATE TABLE TempDB.dbo.Data (id INT, BusNr INT, Seq INT, StopName CHAR (1), Date DATETIME). Dbo.Data VALUES (1,789,1, 'A', '2015-04-08'), (2,789,2, 'b', '2015-04-08'), (3, 789, 3, 'C ',' 2015-04-08 '), (4, 78 9, 4,' D ',' 2015-04-08 '), (5789, 1,' A ',' 2015-04-16 '), (6, 78 9.2,' 'B', '2015-04-16'), (7789, 3, 'D', '2015-04-16'), (8, 78) 9, 1, 'A', '2015-05-10'), (9, 789), 2, 'b', '2015-05-10'), (10,789, 3, 'C' , '2015-05-10'), (11,789, 4, 'D', '2015-05-10'), (12,789,5, 'E', '2015/05/10') Function fnConcatenateStopNames (@BusNr INT, @date DATETIME) declared back as VARCHAR (Max) @RouteStopList VARCHAR (Max start) = '' Select @ RouteStopList = @RouteStopList + ',' + CONVERT (VARCHAR (10), StopName) Seq Returns STUFF (@ RouteStopList, 1, 1, '') from TempDB.dbo.Data D. Where D.BusNr = @BusNr and D.Date = @date order Seq Returns BusNr, closes, orders by BusRouteNumber (more than ROW_NUMBER, closes), by BusRouteNumber (selective DISTINCT D.BusNr, dbo.fnConcatenateStopNames (D.BusNr, D.Date) from TempDB.dbo.Data Prevents D) Data

Edit: Using information Upplied ... against the whole set, which I use the provided function, the stop, the bus, and Fix the date string ... Collect your own path data in a root gate.

In all the routes, you can set the proportion of routes per bus.

If you use it against the posted Rustist as my final statement, then you may possibly extrapolate to stop the service again and again :)