Solved

Create multiple SQL views based off unique members in table

Posted on 2013-06-26
10
378 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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