Solved

Create multiple SQL views based off unique members in table

Posted on 2013-06-26
10
375 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
 

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 39

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 39

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 39

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now