October 3, 2008 at 9:23 am
HI There,
I have 2 tables tab1 and tab2. Below are the signature of the two tables.
Tab1
( name,Description,category)
Tab 2
(Group,Description,f1,f2...fn) --- f(n) - fields of bit type and appears as "name" on the Tab1.
for e.g
tabl
(canedit,editenquiry,security)
(candelete,deleteenquiry,security)
tab2
(g1,g1group,canedit,candelete)
Kindly go through the Tab 2 structure and consider your suggestion for below...
How can I return a temporary table with extra column active added to tab1 by checking the value set on the fields (f1...fn) on tab2.
Any suggestion will be helpful.
Cheers,
Vidhya Rao
October 3, 2008 at 9:32 am
A bit more sample data and perhaps some DDL would go a long way towards getting you an answer, helping us to help you.
In the meantime you may want to research the Pivot Command in BOL and see fi this might not help you.
-Luke.
October 3, 2008 at 11:14 am
HI Luke,
can we use Pivot for bit field data. I have seen people using Pivot for getting aggregate result based on some criteria.
Please find the more samples below
tab1
(CanChange,Changegroup,Security)
(Canedit,changepermission,Accounts)
(canadd,addpermission,Accounts)
(candelete,deletepermission,Accounts)
(canupdate,ChangeGroup,Secuirty)
tab2
(g2,group2,CanChange)
(g2,group2,Canedit)
(g2,group2,canadd)
In the above example we have seen that Field 1 of Tab1 is a column in Tab2 which is of Bit datatype.
Im not sure whether this is achievable by cross tab. Please let me know your suggestions....
Cheers,
Vidhya Rao
October 3, 2008 at 11:49 am
October 3, 2008 at 11:53 am
Luke,
DDL for Tab1 and Tab2 are provided below
Tab1
(
name varchar(25),
description varchar(25),
category varchar(25)
)
October 3, 2008 at 11:58 am
Luke,
Pressed send button accidentally.
Tab1 DDL is as follows
tab1
(
name varchar(25),
description varchar(25),
category varchar(25)
)
tab2
(
groupid varchar(25),
description varchar(25),
field1 bit ,
field2 bit,
..
field(n) bit)
here name from Tab1 is represented as field column in tab2. Im finding if there any possiblity so that i can write single stored procedure that will create a temp table with all the fields from tab1 with active bit field set to yes/no depending on the bit value present in the tab2.
Temp table will have the following structure
create table #temptab
(
name varchar(25),
description varchar(25),
category varchar(25),
active bit --- this value comes from the tab2
)
hope this clears the things.
cheers,
Vidhya Rao
October 3, 2008 at 1:08 pm
I'm still having trouble visualizing what you are trying to do.
So you've given some pseudo DDL but no sample data. Without that all I can do is guess at how you are doing your joins. Rather than field 1, 2, n can you actually give some real test data and DDL so that I may get a full picture of what you are trying to accomplish? If you want to just use 3 columns use 3, but when I asked for sample data before you gave something that did not match with DDL you gave a few posts later.
Perhaps this post from JEff Moden might be of some use in getting the information that we need ion order to help you. http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 3, 2008 at 1:23 pm
HI Luke,
Sorry for all the inconvenience caused.
Tab1 structure
Create Table Tab1
(
name varchar(25),
description varchar(25),
Category varchar(25)
)
Sample data for Tab1
insert into tab1 (name,description,category) values ( 'caneditacc','editing','Acc')
insert into tab1 (name,description,category) values ('candeleteacc','deleting','Acc')
insert into tab1(name,description,category( values ('caneditsec','editing','Sec')
insert in to tab1 (name,description,category( values ('candeletesec','editing','Sec')
this should hopefully populate 4 rows in Tab2
Tab2 structure and datas as follows
create table tab2
(
groupname varchar(25),
caneditacc bit not null,
candeleteacc bit not null,
caneditsec bit not null,
candelete bit not null
)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g1',true,false,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g2',true,ture,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g3',false,false,true,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g4',flase,false,true,true)
)
October 3, 2008 at 1:23 pm
HI Luke,
Sorry for all the inconvenience caused.
Tab1 structure
Create Table Tab1
(
name varchar(25),
description varchar(25),
Category varchar(25)
)
Sample data for Tab1
insert into tab1 (name,description,category) values ( 'caneditacc','editing','Acc')
insert into tab1 (name,description,category) values ('candeleteacc','deleting','Acc')
insert into tab1(name,description,category( values ('caneditsec','editing','Sec')
insert in to tab1 (name,description,category( values ('candeletesec','editing','Sec')
this should hopefully populate 4 rows in Tab2
Tab2 structure and datas as follows
create table tab2
(
groupname varchar(25),
caneditacc bit not null,
candeleteacc bit not null,
caneditsec bit not null,
candelete bit not null
)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g1',true,false,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g2',true,ture,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g3',false,false,true,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g4',flase,false,true,true)
)
October 3, 2008 at 1:29 pm
HI Luke ,
Sorry my laptop is acting weird today.
Previous reply will successfully add 4 rows in tab1 and tab2. Now I just need to know whether I can write a stored procedure that will create temp table with following structure
create table #temptab
(
name varchar(25),
description varchar(25),
Category varchar(25),
active bit not null
)
Active field needs to be populated from the tab2 column values except group field.
I need to compare the name field of the tab1 and column field on tab2 with name ('caneditacc') etc and check its status then populate active field of temp table with appropriate values.
Cheers,
Vidhya Rao
October 3, 2008 at 2:10 pm
Ok, now I have enough data to look at and perhaps understand what you are trying to accomplish.
I do have a couple of more questions though... In your final results table you are looking for name, is that tab1.name or tab2.groupname?
It seems like you'll have to build your temp table and then requery to update each of the rows. You should be able to make this all happen in 1 stored procedure, but you'll have to do a fair amount of manipulation within it.
October 3, 2008 at 6:28 pm
vidhyasudha (10/3/2008)
HI Luke,Sorry for all the inconvenience caused.
Tab1 structure
Create Table Tab1
(
name varchar(25),
description varchar(25),
Category varchar(25)
)
Sample data for Tab1
insert into tab1 (name,description,category) values ( 'caneditacc','editing','Acc')
insert into tab1 (name,description,category) values ('candeleteacc','deleting','Acc')
insert into tab1(name,description,category( values ('caneditsec','editing','Sec')
insert in to tab1 (name,description,category( values ('candeletesec','editing','Sec')
this should hopefully populate 4 rows in Tab2
Tab2 structure and datas as follows
create table tab2
(
groupname varchar(25),
caneditacc bit not null,
candeleteacc bit not null,
caneditsec bit not null,
candelete bit not null
)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g1',true,false,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g2',true,ture,false,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g3',false,false,true,false)
insert into tab2(groupname,caneditacc,candeleteacc,caneditsec,candeletesec) values ('g4',flase,false,true,true)
)
I'm thinking that no crosstab is required here... using the setup code you posted above, please post the desired output 'cause I'm not sure what you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2008 at 2:35 am
HI Luke/Jeff,
Hope you both have seen the two table structure, my result temporary table should hold the values like following
Create table #temptable
(
name varchar(25),
description varchar(25),
category varchar(25),
active bit not null
)
values of the table should look like
caneditacc,editing,Acc,true
candeleteacc,deleting,Acc,false
caneditsec,editing,Sec,true
candelete,editing,Sec,false
active value for this temporary table will come form the table2.Since the table1 and table2 doesnot share any primary/foregin key nor do their data column data type are same , im not sure how my resultant temporary table can be constructed using stored procedure.
Thanks,
Vidhya Rao
October 4, 2008 at 5:39 pm
Aren't you forgetting something in that temp table?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2008 at 3:44 am
HI Jeff,
Temp table will be the structure I mentioned. Two base tables doesn't share any relation. This is due to the fact that database is migrated to sql 2005 with out any change in the datamodel. They have their business application running on ASP and now Im working on their ASP.NET migration. In addition ASP.NET display all the values when creating new group from table 1 and stores the value to the Table2. ie is to say subsequent edit/update will be stored in Table2. I have thought adding extra column in temptable will solve the problem for new/edit operation. Your suggestion is very much valued.
Cheers
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply