Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create multiple SQL views based off unique members in table

Posted on 2013-06-26
10
Medium Priority
?
386 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 2000 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 66

Expert Comment

by:Jim Horn
ID: 39279560
Agreed.  Give us the purpose, and we'll give you the solution.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 41

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 41

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 41

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 49

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

877 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