May 4, 2011 at 11:03 am
Hi,
RDBMS : sqlserver 2005
OS : windows
I have to write a stored proc, for given below scenario
Table-A
id
name
col1
col2
col3
...
coln
we add column for some new values. Col1-n will have only status value "1 or 0 ".
Now I want to write a stored proc that should return
id, name and only those col (from col1-n) that have value 1.
I can not hard code col name (col1-n) as in future col can increase, so the stored proc should return always right results.
can you please help me ?
May 4, 2011 at 11:08 am
Almost impossible to write with efficient query. More to the point, no a job to do in sql server.
Just grab everything and manage that in the presentation layer.
May 4, 2011 at 11:51 am
As Ninja put it, the solution might not be an efficient one. Can be done in 3 parts.
1. UNPIVOT your columns - this will be dynamic as u don't know the name or number of columns
2. Filter the result for values = 1
3. Pivot them back - this will also be dynamic as u don't know the name or number of columns
Not a efficient solution, but does what you want.
May 4, 2011 at 12:48 pm
If you have such a bad design to your database that you are adding columns to a table dynamically, then you won't care about more bad code.
It's pretty easy actually: query sys.columns for the columns of your table and build a where clause in a string (preferably in a cursor). Now plop that where string onto a select and EXEC the whole thing at the end of the procedure. BAM! Dynamic SQL FTW!
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 4, 2011 at 1:05 pm
thbaig1 (5/4/2011)
Hi,RDBMS : sqlserver 2005
OS : windows
I have to write a stored proc, for given below scenario
Table-A
id
name
col1
col2
col3
...
coln
we add column for some new values. Col1-n will have only status value "1 or 0 ".
Now I want to write a stored proc that should return
id, name and only those col (from col1-n) that have value 1.
I can not hard code col name (col1-n) as in future col can increase, so the stored proc should return always right results.
can you please help me ?
Your best bet is to look at the Columns that are going to be 0 or 1 and instead make them a separate table storing the "column" names. You then link that table back through an "interim" table that allows you to create a Many to Many relationship.
It should look something like
CREATE TABLE Table-A
(AID int,
AName varchar(20)
)
CREATE TABLE YourColNames
(YourColNamesID int,
YourColNamesDescription varchar(40)
)
CREATE TABLE Table-A_YourColNames
(AID int,
YourColNamesID int
)
This will let you link as many items from YourColNames to as many items in Table-A as you need. The query to get the data back in the format you want would be
SELECT AName, YourColNamesDescription
FROM Table-A A
INNER JOIN Table-A_YourColNames AY
ON A.AID = AY.AID
INNER JOIN YourColNames Y
ON AY.YourColNamesID = Y.YourColNamesID
ORDER BY A.AName
This also lets you add as many "columns" as you want without having to change any code, you just make a new entry in the YourColNames table and the corresponding linking entries in the "interim" table called Table-A_YourColNames
If you'd like, post your Table definition and some sample data and I'd be happy to help you work out the new structure.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 4, 2011 at 1:53 pm
ColdCoffee (5/4/2011)
As Ninja put it, the solution might not be an efficient one. Can be done in 3 parts.1. UNPIVOT your columns - this will be dynamic as u don't know the name or number of columns
2. Filter the result for values = 1
3. Pivot them back - this will also be dynamic as u don't know the name or number of columns
Not a efficient solution, but does what you want.
thank you.
can you please explain a little more. I think it can work
May 4, 2011 at 2:51 pm
thbaig1 (5/4/2011)
ColdCoffee (5/4/2011)
As Ninja put it, the solution might not be an efficient one. Can be done in 3 parts.1. UNPIVOT your columns - this will be dynamic as u don't know the name or number of columns
2. Filter the result for values = 1
3. Pivot them back - this will also be dynamic as u don't know the name or number of columns
Not a efficient solution, but does what you want.
thank you.
can you please explain a little more. I think it can work
Involves lot of work.. Can you set me up an environment to work, as in, can you provide CREATE table scripts of your table, some 100 random records as INSERT INTO table scripts ?
May 5, 2011 at 8:02 am
I had some time to play. Try this out, thbaig1.
create table test (ID int, col1 bit, col2 bit);
insert into test (ID, col1, col2)
select 1, 1, 1 union all select 2, 0, 0;
go
create procedure select_from_test as
set nocount on
declare @col_name varchar(100);
declare @sql varchar(max);
set @sql = '';
declare rs cursor for
select c.name from sys.columns c
inner join sys.tables t on c.object_id = t.object_id and t.name = 'test';
open rs;
fetch next from rs into @col_name;
while @@FETCH_STATUS = 0
begin
if LEFT(@col_name,3) = 'col'
set @sql = @sql + 'isnull(' + @col_name + ',0)=1 and ';
fetch next from rs into @col_name;
end;
close rs;
deallocate rs;
set @sql = @sql + '1=1';
set @sql = 'select * from test where ' + @sql;
exec (@sql);
go
exec select_from_test;
alter table test add col3 bit;
insert into test (ID, col1, col2, col3)
select 3, 1, 1, 1;
exec select_from_test;
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 5, 2011 at 8:08 am
That's only 10% of the problem.
Run the same thing with 600 columns and 10 000 000 rows of data. :hehe:
May 5, 2011 at 8:12 am
Ninja's_RGR'us (5/5/2011)
That's only 10% of the problem.Run the same thing with 600 columns and 10 000 000 rows of data. :hehe:
This and flexibility with the addition of additional columns in the future are why I suggested rewriting the table as a many to many realtionship. I strongly suggest you go that route and save yourself a lot of pain in the future.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 8:27 am
I agree with you wholeheartedly, Ninja. But this will work fine for small to moderate size tables. That's also something for the OP to figure out, when to use a certain solution or just redesign the whole thing. Right now he has laser sight on fixing this problem, so he'll like the code I have. He needs to realize that Stefan's solution above is optimal.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 5, 2011 at 8:29 am
toddasd (5/5/2011)
I agree with you wholeheartedly, Ninja. But this will work fine for small to moderate size tables. That's also something for the OP to figure out, when to use a certain solution or just redesign the whole thing. Right now he has laser sight on fixing this problem, so he'll like the code I have. He needs to realize that Stefan's solution above is optimal.
Agreed, but then again doing that at presentation time is actually the optimal solution...IMHO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply