473,581 Members | 2,668 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dyamic view/function based on table data (?)

Hey,

First, sorry if this post appear twice, because, I can not find my post
recently send, trying to post it once again.
I'm out of ideas, so, I thought, will search help here again :(

I'm trying to prepare a view for ext. app. This is in normal cases very
easy, but what if the view structure should be dynamic?!

Here is my point (I will siplify the examples).

I have a table:

create table t_data (
id bigint identity (1,1) not null,
val varchar(10) not null,
data varchar(100) not null
constraint [PK_t_data] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_data (val, data) values
('1111111111',' 1234567890abcde fghijklmnoprstu vwxyz1234567890 abcdefghijklmno prstuvwxyz67890 abcdefghijklmno prstuvwxyz')
insert into t_data (val, data) values
('2222222222',' 1234567890abcde fghijklmnoprstu vwxyz1234567890 abcdefghijklmno prstuvwxyz12345 abcdefghijklmno prstuvwxyz')
insert into t_data (val, data) values
('3333333333',' 12345abcdefghij klmnoprstuvwxyz 1234567890abcde fghijklmnoprstu vwxyz1234567890 abcdefghijklmno prstuvwxyz')
insert into t_data (val, data) values
('4444444444',' 67890abcdefghij klmnoprstuvwxyz 1234567890abcde fg12345hijklmno prstuvwxyz67890 abcdefghijklmno prstuvwxyz')
insert into t_data (val, data) values
('5555555555',' 1230abcdefghijk lmnoprst12345uv wxyz1234567890a bcdefghijklmnop rstuvwxyz67890a bcdefghijklmnop rstuvwxyz')
go

create table t_dataVal (
id bigint identity (1,1) not null,
val varchar(10) not null,
fill varchar(4) not null
constraint [PK_t_dataVal] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )

go

insert into t_dataVal (val, fill) values ('1111111111',' AAAA')
insert into t_dataVal (val, fill) values ('2222222222',' KKKK')
insert into t_dataVal (val, fill) values ('3333333333',' DDDD')
insert into t_dataVal (val, fill) values ('4444444444',' ZZZZ')
insert into t_dataVal (val, fill) values ('5555555555',' CCCC')
go

create table t_conf (
id bigint identity (1,1) not null,
start int not null,
length int not null,
description varchar(20) not null,
constraint [PK_t_conf] primary key clustered
(
id
) with fillfactor = 90 on [PRIMARY] )
go

insert into t_conf (start, length, description) values (1,10,'value_1' )
insert into t_conf (start, length, description) values (11,3,'value_2' )
insert into t_conf (start, length, description) values
(55,15,'value_3 ')
insert into t_conf (start, length, description) values (33,2,'value_4' )
insert into t_conf (start, length, description) values (88,1,'value_5' )
insert into t_conf (start, length, description) values (56,7,'value_6' )
go

Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table

So the effect should be like this:
val fill value_1 value_2 value_3 value_4 value_5 value6
1111111111 AAAA 1234567890 abc ....
2222222222 KKKK 1234567890 abc ....
3333333333 DDDD 12345abcde fgh ....
4444444444 ZZZZ 67890abcde fgh ....
5555555555 CCCC 1230abcdef ghi ....

of course, for all other value_x should be the appropriate substrings
shown.

Sounds simple, hm?
Well, I'm trying to do this, since yesterday evening, and can not :(

In real life, the call of view/function might happend a lot.
The table t_data might have around 4000 records, but the data string is
longer (around 3000 characters).

Application, might acess a udf, which returns table, and I was focusing
in that.
Was trying, to create local temp table in function, to insert values,
using cursor over t_conf.
Unfortunately, everything what I get, is just a vertical representation
of the data, and I need it horizontal :(
The other problem in function is, that I can not use exec() (wll known)
so I can not even create a table,
dynamicly, using as column names description value from table t_conf,
and as size of field length from this table.

Sorry, that the description is maybe not exactly for my problem, but
this is because I'm not even sure, which way to use :(

any help will be appreciated!

Thank You - Matik

Sep 21 '06 #1
1 2968
Matik (ma****@sauron. xo.pl) writes:
Now here is the issue:
table t_conf contain data, which can be modified by user. The user is
seting the appropriate values.
Now, there should be a view, which returns:
- as headers (collumn names) this what is defined in description column
of t_conf (for example: value_1, value_2 ... value_6)
- as values, substrings of all data from t_data, cutted with start and
length values for appropriate decription from t_conf.
- first two columns of view, should be column val and fill of t_dataVal
table
A view, just like a table, has a fixed number of columns, so it can't
be dynamic. Theoretically, you could have a trigger on the configuration
table that recreates the view each time some adds or removes a value.
I'm not really sure that I like this alternative a lot.

Overall, the entire design seems unsound to me. What would the point be
to store all data in a single column, and then use another table to
specify where the value boundaries are. I could guess that this is an
import table, but even in that case I would question if the field
definition should be apply before the data makes it to SQL Server.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 21 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
2754
by: Droolboy | last post by:
I'm trying to build a fairly small (max. 10 different pages) site using php, and it's becoming obvious that I need some kind of model view separation. Having done a few searches, I've come across the concept of 'template engines', but that seems like overkill for my needs. I'm thinking I'll just stick with php as my template language; a...
2
1596
by: James Johnson | last post by:
I have 2 tables and am displaying data in a single table on a web page from both tables. Most of the data that I'm displaying is in table 2 but some is in table 1 (lastname, firstname). I'm using "order by" to allow sorting table 2 using hyperlinks, but I haven't figured out how to allow sorting the "view" based on the lastname/firstname from...
4
2344
by: Ryan | last post by:
Bit of an obscure one here, so please bear with me. I have two copies of a database which should be identical. Both have a complex view which is identical. I can open the views and the data is as expected and match. I can query it in several ways as detailed below. The 5th version of the simple query below based on the second copy of the view...
4
11254
by: sci | last post by:
Could someone help me by answering the questions below? What's a cursor? What's difference between Query and View? Is a RecordSet just part of a table? Can it be part of a query of view? If the content in a table changed, is it necessary for a old recordset to renew itself by do "Requery()"? Thanks for your help!
2
2320
by: Hennie de Nooijer | last post by:
Because of an error in google or underlying site i can reply on my own issue. Therefore i copied the former entered message in this message. -------------------------------------REPY---------------------------------- Hi Maybe i wasn't clear. I want to dynamically check whether what the lowest date and the highest date is in the calendar table....
20
2668
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about 25-50). This allows each user to have their own set of selections. The selections table has three fields: ID (int), Sel (bit), MachName (varchar). ID...
4
2076
by: JayCallas | last post by:
I have a SQL 2000 table containing 2 million rows of Trade data. Here are some of the columns: INT IDENTITY(1,1) -- PK, non-clustered DATETIME -- clustered index DATETIME -- non-clustered index VARCHAR(10) VARCHAR(10) INT etc..
2
9724
by: Sam | last post by:
A) Destination Table with 4 Fields. 1) Last Name 2) First Name 3) State 4) Zip Code. B) Look up table State/Zip Code with 2 Fields
0
2390
by: Jacob Donajkowski | last post by:
Once the user logs in I want to have the users switch from the Roster View to the Profile View and enter their profile infomation and save it. Then the next time they login and go to the Profile View I want the form populated from there profile on the sql server. The code to save the profile works fine. But when the user logs back in they data...
0
7797
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8151
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8176
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6555
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5677
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5365
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3830
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2302
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1139
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.