Create multiple SQL views based off unique members in table
I have a table in SQL Server 2008 R2 that I would like to create multiple views from.
Basically, for each distinct value in a column, I would like to create a view with the name of that value. Each view will have the same columns as the original table, but with only the rows where the column=unique value.
Example in included picture.
View = CountyA with rows 1-2
View = CountyB with row 3
View = CountyC with rows 4-8
View = CountyD with row 9
View = CountyE with row 10
View = CountyF with row 11
What would be the best way to go about writing this query?
Basically, for each distinct value in a column, I would like to create a view with the name of that value. Each view will have the same columns as the original table, but with only the rows where the column=unique value.
Example in included picture.
View = CountyA with rows 1-2
View = CountyB with row 3
View = CountyC with rows 4-8
View = CountyD with row 9
View = CountyE with row 10
View = CountyF with row 11
What would be the best way to go about writing this query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agreed. Give us the purpose, and we'll give you the solution.
ASKER
There are going to be 100 counties actually. The names of the counties will not change, but the data will updated every month.
The purpose of creating these views is that they need to be used as input into an existing ArcGIS model. The model requires tables of records for each county to be used as an input.
I've attached the image -- missed the upload file button before the submit.
Thanks!
Screenshot-1.jpg
The purpose of creating these views is that they need to be used as input into an existing ArcGIS model. The model requires tables of records for each county to be used as an input.
I've attached the image -- missed the upload file button before the submit.
Thanks!
Screenshot-1.jpg
You can't do it with a view but you can do it with a function:
create function fn_GetRows(@county varchar(50))
returns TABLE
as
return
(
with cte as
(
select row_number() over(order by <PK>) row, <other columns>
from <table>
),
cte2 (row) as
/* note that the strings are hardcoded, those can be combined in another query to be populated from a table */
(select case when @county = 'CountyA' then ',1,2,'
when @county = 'CountyC' then ',4,5,6,7,8,'
when @county = 'CountyD' then ',9,'
END
)
select * from cte where charindex(',' + cast(row as varchar(100)) + ',', (select row from cte2), 0) >0
);
=================
to execute:
select * from fn_GetRows('CountyC')
create function fn_GetRows(@county varchar(50))
returns TABLE
as
return
(
with cte as
(
select row_number() over(order by <PK>) row, <other columns>
from <table>
),
cte2 (row) as
/* note that the strings are hardcoded, those can be combined in another query to be populated from a table */
(select case when @county = 'CountyA' then ',1,2,'
when @county = 'CountyC' then ',4,5,6,7,8,'
when @county = 'CountyD' then ',9,'
END
)
select * from cte where charindex(',' + cast(row as varchar(100)) + ',', (select row from cte2), 0) >0
);
=================
to execute:
select * from fn_GetRows('CountyC')
I like my solution I posted earlier still. Use a cursor and generate the SQL statements you need to create the tables. 100 tables seems like too many to do by hand. Even if you don't need to re-create the views every month or whatever, you might spend less time writing something to generate the SQL than you will manually creating 100 views.
You would still have to point at 100 different views,
where as the solution above is 1 function, and the data returned is different based on the county.
In fact, why can't you just add a countyName column to the table so you associate the rows directly, this way the data is automatically stored in the table with no need to adjust the logic.
select * from table where countyName = 'CountyA' and you're done.
where as the solution above is 1 function, and the data returned is different based on the county.
In fact, why can't you just add a countyName column to the table so you associate the rows directly, this way the data is automatically stored in the table with no need to adjust the logic.
select * from table where countyName = 'CountyA' and you're done.
From author's post:
Agreed, if I were writing my own application, I wouldn't use 100 views, I would use 1 query, but that's not the situation here. :)
The model requires tables of records for each county to be used as an input.The point is, he *needs* 100 different views as that's what his GIS software wants. It wants a table name, not a piece of SQL.
Agreed, if I were writing my own application, I wouldn't use 100 views, I would use 1 query, but that's not the situation here. :)
I missed that post with the image. If it's truly the case you need a view for each county then the cursor loop would definitely be the way to go.
Was going off the assumption that he needed to pass back specific row IDs and there was no connection to the county itself on that table.
Was going off the assumption that he needed to pass back specific row IDs and there was no connection to the county itself on that table.
>>the model requires tables of records for each county to be used as an input.
does the model literally require individual tables as input, or are views of one table acceptable?
If views are Ok:
Once a view per county exists, they do not need to be redefined, each per-county view will automatically provide the most recent data from the source table.
The only need for view re-definition would be if the source table structure changed.
and/or New views would be needed for any new county
(as an additional note, the county name would need to suit use as a dbms object name)
In the proposed dynamic code to generate views there will need to be logic to drop an existing view of the same name before proceeding to (re)create it.
I would like to know though: "does the model literally require individual tables?"
(hope not)
does the model literally require individual tables as input, or are views of one table acceptable?
If views are Ok:
Once a view per county exists, they do not need to be redefined, each per-county view will automatically provide the most recent data from the source table.
The only need for view re-definition would be if the source table structure changed.
and/or New views would be needed for any new county
(as an additional note, the county name would need to suit use as a dbms object name)
In the proposed dynamic code to generate views there will need to be logic to drop an existing view of the same name before proceeding to (re)create it.
I would like to know though: "does the model literally require individual tables?"
(hope not)
Also, there is no picture attached.