March 25, 2008 at 4:12 am
Is it possible using a Stored procedure to return the field names that contain a certain value?
as an example
date field1 field2 field3 field4 field5 field6
-------------------------------------------------------
25/12/2007 Jim John Jim Steve Jim David
26/12/2007 John Steve Steve John David Jim
What I'm hoping to achieve is something like return all fields that contain 'Jim' for 25/12/2008
so I'd get
field1
field3
field5
I'd appreciate any help possible on this
March 25, 2008 at 4:29 am
Hi...
Assuming that each date returns just one row this should work:
declare @field1 varchar(128), @field2 varchar(128), @field3 varchar(128), @field4 varchar(128), @field5 varchar(128), @field6 varchar(128)
select @field1 = case when field1 = 'jim' then 'field1' else null end
, @field2 = case when @field2 = 'jim' then 'field2' else null end
, @field3 = case when field3 = 'jim' then 'field3' else null end
, @field4 = case when field4 = 'jim' then 'field4' else null end
, @field5 = case when field5 = 'jim' then 'field5' else null end
, @field6 = case when field6 = 'jim' then 'field6' else null end
from my_table
where date = '25/12/2007'
select * from
(
select @field1 as field_name
union
select @field2
union
select @field3
union
select @field4
union
select @field5
union
select @field6) as fields
where field_name is not null
March 25, 2008 at 4:55 am
Thanks Karl, the only problem I have is there are 96 fields at the moment and that could change to more at a later date, your solution, is that the only way of doing it? incidently, the date field will be unique
March 25, 2008 at 5:38 am
mick burden (3/25/2008)
Thanks Karl, the only problem I have is there are 96 fields at the moment and that could change to more at a later date, your solution, is that the only way of doing it? incidently, the date field will be unique
Hi Mick,
here's another possible solution, which involves using a cursor to go through each column name and also uses dynamic SQL, which isn't ideal. I've cut a few corners with the code (the dynamic SQL in particular) but you should get the idea.
I haven't tested if this works so there might be a few syntax errors but I think the logic is sound. Whether there's another way of doing this that avoids dynamic SQL I'm not sure.
create table ##field_name (field varchar(128))
--insert into temp table the row we want so that we don't have to select from the table later on.
--need to perform a select * into because we don't know the columns ahead of time.
select *
into ##my_table
from my_table
where date = '25/12/2007'
declare @column varchar(128)
--cursor through all of the columns in our table
declare col_cursor cursor
for
select column_name
from information_schema.columns
where table_name = 'my_table'
open col_cursor
fetch next from col_cursor into @column
while @@fetch_status = 0
begin
--if the current column contains 'jim' then let's insert into our ##field_name table
exec('if exists (select 1 from ##my_table where [' + @column '] = ''jim'') insert into ##field_name(field) values(''' + @column + ''')')
fetch next from col_cursor into @column
end
close col_cursor
deallocate col_cursor
--select all of our field names
select * from ##field_name
drop table ##field_name
drop table ##my_table
March 25, 2008 at 5:44 am
Many thanks Karl, I did suspect using a cursor would be one solution, the temporary table idea wasn't something I thought of and looks like this might do more or less what I need. Thanks again for coming up with a solution so quick
March 25, 2008 at 6:23 am
Just curious as to what your data is modeling...perhaps a different table design would make life easier.
Rather than have these multiple columns (up to 96 as you say), it may be possible to remodel this into 2 tables, one holding the list of names, the 2nd containing a row for each instance of a name and also the value that would be in the 'fieldx' for that person. That way, when you have an additional 'fieldx', you add rows to the table (data), as opposed to having to add a column (which is a structure change).
I'm just speculating as I don't know what you are doing, but if you think this merits additional discussion, post some more information, and the community can help you out.
If it was easy, everybody would be doing it!;)
March 25, 2008 at 8:28 am
I'm not sure that splitting it into 2 tables would help, the senario I'm using it in is at work there are 96 different towns or regions that we as a team work too, I normally allocate 6 to 8 such towns for each person to work too, this could change daily. some other software will query the database to find out who's doing a particular town today and allocate other info to that person, I did try one time making each person a field (there are 15 of us) and putting multiple towns under each heading seperated by |, this worked most of the time, but with changes going on all the time duplicates were creeping in, so I decided to create seperate fields(towns) with only one name in each, hope this makes sence to you
March 25, 2008 at 8:49 am
I would model it like this because once you have built the table structure, you have the flexibility to add people and towns as your needs change without have to change the table structure. It is much easier to edit data (via GUI, stored procedures, etc) than to have to continue to modify the table structure.
Use 3 tables (name these what you want, add additional fields as necessary).
Person (fields PersonID, PersonName)
Town (fields TownID, TownName)
PersonTown (fields PersonTownID, PersonID, TownID, WorkDate)
You keep your list of people in Person, list of towns in town (add to it as necessary), then make the association between the 2 in PersonTown.
To get the assignments for any given day...
Select WorkDate, PersonName, TownName
From PersonTown PT
inner join Person P
on PT.PersonID = P.PersonID
inner join Town T
on PT.TownID = T.TownID
where WorkDate = [some date here] --whatever date you need
Pros: As people and towns change, you just add them to their respective table without having to make a table structure change.
Cons: If you have been editing the data by opening up the table and editing directly, that method will be difficult with this. You will be better to edit your data using a stored proc or build a GUI front end.
If you need the data output in a different format (than the above SELECT), there are many ways to do it...your aren't limited to this one.
Hope this helps!
If it was easy, everybody would be doing it!;)
March 25, 2008 at 8:59 am
That's great, thanks, even if I don't use the example you supplied it's great to learn from. Thanks again
April 21, 2008 at 7:29 am
Sam, I'm having a go at your example, the only thing I'm not too clear on is the field types, are the INT type an ID type or just INT
April 21, 2008 at 7:37 am
IDs will work well here. PersonID and TownID should be "int identity" data type, and should be the primary keys for those two tables.
(By the way, what Sam is recommending to you is what's called "normalization", or "normal form". Some of the articles on that subject get a bit thick a bit fast, but it's a key concept in database design, and if you get the basic idea, it will help you a lot. Wikipedia has okay articles on this currently, subject to change without notice 🙂 .)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 7:40 am
many thanks for the quick reply
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply