Solved

Create multiple SQL views based off unique members in table

Posted on 2013-06-26
10
377 Views
Last Modified: 2013-06-27
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?
0
Comment
Question by:VAMS1
10 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 39279540
What are you trying to accomplish by doing this?  Might be able to provide a better solution if we have move details.


Also, there is no picture attached.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39279557
Uhm... no picture. ;-)

If it's just these 6 views, I would go ahead and just write the CREATE VIEW statements for them manually.

If these values will change often in short periods of time, meaning you'll have to re-create the views often, then I would use dynamic SQL to create them (write a series of SQL statements, most likely with a CURSOR, to *generate* the CREATE VIEW statements).  Thus, the process would be automated and I could put this all in a script or stored procedure and call it whenever I want the views updated.

For example, something like this (this is pretty rough/non-working code):

DECLARE @SQL NVARCHAR(2000);
DECLARE @county VARCHAR(100);
DECLARE CURSOR county_cur FOR
  SELECT DISTINCT County
  FROM whatever_table
  ORDER BY County
;
OPEN CURSOR county_cur

FETCH NEXT from county_cur INTO @county;
WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @SQL = 'CREATE VIEW view_' + @county + ' + CHAR(10)
      + ' AS' + CHAR(10)
      + ' SELECT field1, field2, field3, county, whatever' + CHAR(10)
      + ' FROM whatever_table' + CHAR(10)
      + ' WHERE county = ''' + @county + '''' + CHAR(10)
      + ';'
  EXEC (@SQL);
  FETCH NEXT from county_cur INTO @county;
END

CLOSE county_cur
DEALLOCATE county_cur

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39279560
Agreed.  Give us the purpose, and we'll give you the solution.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:VAMS1
ID: 39279590
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
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39279596
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')
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39279756
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.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39279778
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.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39279787
From author's post:
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. :)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39279831
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280182
>>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)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL Query to include null values 3 33
Sql Stored Procedure field variable 17 31
sql 2014,  lock limit 5 32
SQL Error - Query 6 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question