SQL views from one table

My colleague asked me a question regarding getting data from a SQL Server database.

I have a sample data set

  • From xml to table using sql server
  • Assign variable in inner SELECT statement
  • SQL Server equivalent of MySQL Dump to produce insert statements for all data in a table
  • SQL query to replace | in address field to front
  • Max Row Size in SQL Server 2012 with varchar(max) fields
  • Access to Sql Server via ODBC from C# : ERROR Data source name not found and no default driver specified
  • [ID],[TOWN],[PERSON]

    Now from this I would like to return a SQL Dataset that returns me a different table based upon the view.

    Essentially in code I could get a distinct on the town then loop sql filtering on the town. But is there a way I can do this in SQL?

    Where I would get (3) views back (2 Belfast, 1 Newry and 1 Larne)

    Basicly I it would return


    Then another view would return for ‘Larne’ and a Final one for Newry. Basically SQL creating views for each town it finds and then returns the records for each town.

    3 Solutions collect form web for “SQL views from one table”

    You don’t get views back – you have to define them yourself.

    E.g. if you need one view for Belfast, a second for Newry and a third for Larne – then you need to create three views that return only those rows that match the relevant city name

     CREATE VIEW BelfastView 
        SELECT ID, Town, Person
        FROM dbo.Towns
        WHERE Town = 'Belfast'
     CREATE VIEW LarneView 
        SELECT ID, Town, Person
        FROM dbo.Towns
        WHERE Town = 'Larne'
     CREATE VIEW NewryView 
        SELECT ID, Town, Person
        FROM dbo.Towns
        WHERE Town = 'Newry'

    Now, certain users might only be allowed to select data from the BelfastView and thus would never see any other rows of data from your underlying table.

    But views are database objects like tables or stored procedures; you need to create them, maintain them, toss them when no longer needed.


    Based on your updated question, you simply need to create a view for each town you want to filter:

    CREATE VIEW BelfastView AS
    SELECT    ID,
    FROM      YourTable
    WHERE     Town = 'BELFAST'

    Although you’ve only given us a small sample of your data, what you’re asking is almost never a good idea. What happens when you have 50 new towns in your DB? Are you going to create a view for each town? This does not scale well (or at all).

    Basically I have decided to Run it as a Stored Procedure to return me each item as a List. So something along the lines of this:

    Create Procedure ListTowns
    declare @towns char(11)
    select @towns = (select distinct Town from [Towns])
    while @towns is not null <> 0
        select * from [YourTable] where Town = @towns
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.