Concatenate multiple column results - newbie

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Concatenate multiple column results - newbie

Author

Topic

shasta
Starting Member

3 Posts

Posted - 2009-09-18 : 11:20:57

I have a table that gets its data from a web form. On this web form are many checkboxes that the customer selects the types of products they’re interested in. Each of these checkboxes represents a column (bit data type) in my table. I would like to be able to select only the boxes that have a 1 in them, and return a string that says which product the customer is interested in. For example:
Columns equal:
ropes | boots | hooks | gloves | mittens
1 | 0 | 1 | 1 | 0
0 | 1 | 0 | 1 | 1

With a single column result:
ropes, hooks, gloves
boots, gloves, mittens

My SQL scripting knowledge is pretty limited, so any help you can provide would be really appreciated!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-09-18 : 11:30:35

select (case ropes when 1 then 'Ropes, ' else '' end +
case Boots when 1 then 'Boots, ' else '' end +
case hooks when 1 then 'hooks, ' else '' end +
case gloves when 1 then 'gloves, ' else '' end +
case mittens when 1 then 'mittens, ' else '' end ) as string

From yourtable

An infinite universe is the ultimate cartesian product.

shasta
Starting Member

3 Posts

Posted - 2009-09-18 : 12:17:15

EXCELLENT!! And thanks so much for the quick response. I really appreciate the help.
Though I realize now, we have way too many products! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources