|
|
Posted by Matik on 09/21/06 13:29
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','1234567890abcdefghijklmnoprstuvwxyz1234567890abcdefghijklmnoprstuvwxyz67890abcdefghijklmnoprstuvwxyz')
insert into t_data (val, data) values
('2222222222','1234567890abcdefghijklmnoprstuvwxyz1234567890abcdefghijklmnoprstuvwxyz12345abcdefghijklmnoprstuvwxyz')
insert into t_data (val, data) values
('3333333333','12345abcdefghijklmnoprstuvwxyz1234567890abcdefghijklmnoprstuvwxyz1234567890abcdefghijklmnoprstuvwxyz')
insert into t_data (val, data) values
('4444444444','67890abcdefghijklmnoprstuvwxyz1234567890abcdefg12345hijklmnoprstuvwxyz67890abcdefghijklmnoprstuvwxyz')
insert into t_data (val, data) values
('5555555555','1230abcdefghijklmnoprst12345uvwxyz1234567890abcdefghijklmnoprstuvwxyz67890abcdefghijklmnoprstuvwxyz')
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
Navigation:
[Reply to this message]
|