Databases
StackOverflow2013
Postsdbo
POProblem with a Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblem with a Query
    primarykey
    data
    text
    <p>I am working on a routing system for a transportation type similar to a bus routing system like below. I have a view which gives me output like this. I need pivoting over schedules where the number of schedules can be of variable quantity. </p> <p>My query should result in output given below in the image. I tried using Case Statements but I had problems with number of rows returned.</p> <p><a href="https://i.stack.imgur.com/kumTw.jpg" rel="nofollow noreferrer">http://i.stack.imgur.com/kumTw.jpg</a> <img src="https://i.stack.imgur.com/kumTw.jpg" alt="" title="Hosted by imgur.com" /> ![enter image description here][1]</p> <p>Here are the scripts to generate the table and the data for reference:</p> <pre><code>IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScheduleTest]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ScheduleTest]( [StationName] [nvarchar](255) NULL, [ScheduleNumber] [nvarchar](255) NULL, [ArrivalTime] [nvarchar](20) NULL, [DepartureTime] [nvarchar](20) NULL ) ON [PRIMARY] END GO --Insert Scripts For Schedule A Insert into ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleA', NULL, '02:45') Insert into ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleA', '02:55', '03:00') Insert into ScheduleTest Values ('Benton, MI, Harbor', 'ScheduleA', '08:00', NULL) --Benton in this case --is final destination so departure time is null -- Insert Scripts for Schedule B (Another Which runs in the morning) Insert into ScheduleTest Values ('Chicago, IL, (Union Station)', 'ScheduleB', NULL, '06:00') Insert into ScheduleTest Values ('Chicago, IL, (DownTown)', 'ScheduleB', '06:10', '06:15') Insert into ScheduleTest Values ('Benton, IL, Harbor', 'ScheduleB', '11:00', NULL) </code></pre> <p>I don't think I can use pivoting in Sql server 2005 since it needs some sort of aggregates to work on. I have nothing aggregate here.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAbe Miessler
    1. USKunal Uppal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProblem with a Query
      1. USjon_darkstar
      1. VTFavorite
    2. VO
      singulars
      1. POProblem with a Query
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POProblem with a Query
      1. USTBohnen.jnr
      1. VTFavorite
    1. COYou should think about a more specific title.
      singulars
      1. POProblem with a Query
      1. USuser unknown
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload