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
[ID],[TOWN],[PERSON] ,[BELFAST],[JAMES] ,[NEWRY],[JOHN] ,[BELFAST],[SIMON] ,[LARNE],[ALAN]
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
[ID],[Town],[Person] ,[Belfast],[James] ,[Belfast],[Simon]
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 AS SELECT ID, Town, Person FROM dbo.Towns WHERE Town = 'Belfast' CREATE VIEW LarneView AS SELECT ID, Town, Person FROM dbo.Towns WHERE Town = 'Larne' CREATE VIEW NewryView AS 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, Town, Person 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 As declare @towns char(11) select @towns = (select distinct Town from [Towns]) while @towns is not null <> 0 begin select * from [YourTable] where Town = @towns end