January 4, 2013 at 9:32 am
I think this should be a case statement anyway. I have a table storing items and their options, so it looks like this:
ID
Desc
opt1
opt1
opt3
opt4
opt5
etc
The option fields are all BIT type fields for true/false. Now I need one more field to summarize all the options for search purposes, so I'm trying to add an nvarchar field and I would like to populate it with the options, so for each row, if opt1, opt3, and opt5 are TRUE, then the field OPTSUMMARY would contain "opt1, opt3, opt5"
then my search form can just search on this 1 field for any of the options.
Any help is greatly appreciated, thanks!
January 4, 2013 at 10:06 am
This is a rather bizarre requirement to say the least. I do not condone doing this sort of thing to make searching easier. The ease of writing an easy search like this comes at the cost or performance. For a better methodology of searching I would recommend you look at Gail's article about catch all searches. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
If however, you are deadset on doing this your way you can accomplish this with computed columns.
IF OBJECT_ID('tempdb..#Options') IS NOT NULL
DROP TABLE #Options
create table #Options
(
ID int identity,
Descript varchar(30),
opt1 bit,
opt2 bit,
opt3 bit
)
insert #Options
select 'some Value', 1, 0, 0 union all
select 'Another', 0, 1, 1 union all
select 'and again', 0,0,0
select * from #Options
--Now we add our computed column
alter table #options
add SearchCol as (isnull(case when opt1 = 1 then 'opt1, ' end, '') + isnull(case when opt2 = 1 then 'opt2, ' end, '') + isnull(case when opt3 = 1 then 'opt3' end, '')) PERSISTED
select * from #Options
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 10:07 am
I would also ask that you notice how I posted ddl and sample data in an easily consumable format. You should do something like that on future posts. It makes it a lot easier for us to help.
And last but not least, your table could really stand some normalization. What happens when you add an opt6 or 7? You have to change the table and all the queries that access it. Yuck!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 10:57 am
CELKO (1/4/2013)
I think this should be a CASE statement anyway.
SQL has a CASE expression, not a CASE statement; totally different concept!
I thought of you as I was typing in my response Joe. I even had some text to this point and decided not to bother. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply