Concatenating conditional results

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 2000 Forums

Transact-SQL (2000)

Concatenating conditional results

Author

Topic

jackdewey
Starting Member

7 Posts

Posted - 2004-04-07 : 13:43:29

I have a table with flags set to True or False on Multiple Fields for various properties of my record.

e.g.

ACCOUNTID PRODUCT1ACTIVE PRODUCT2ACTIVE PRODUCT3ACTIVE
1 T T F

I want to setup a query that would for each account return a single string listing the products where the value of the column is set to T.

In the example above, I would like the result to be:
"PRODUCT1 PRODUCT2"

I would really appreciate any help you can provide on this. Thanks in advance,

Yannick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-07 : 14:00:02

SELECT Product1Act = CASE WHEN Product1Active = 'T' THEN 'PRODUCT1' ELSE '' END,
Product2Act = CASE WHEN Product2Active = 'T' THEN 'PRODUCT2' ELSE '' END,
Product3Act = CASE WHEN Product3Active = 'T' THEN 'PRODUCT3' ELSE '' END
FROM Table1

Tara

jackdewey
Starting Member

7 Posts

Posted - 2004-04-07 : 14:07:05

Thank you Tara. However, this returns three fields and I need to only get one string back with all of the product names delimited by spaces. I tried adding + signs as you can usually do to concatenate data in a select statement, but I did not find a good place to put them to get a valid statement.

Thanks again,

Yannick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-07 : 14:08:34

Try this:

http://www.sqlteam.com/item.asp?ItemID=11021

Instead of comma separate values, you'd have space separated values.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-07 : 14:08:36

SELECT ActiveProducts = LTRIM(RTRIM(
CASE WHEN Product1Active = 'T' THEN 'PRODUCT1' ELSE '' END + ' ' +
CASE WHEN Product2Active = 'T' THEN 'PRODUCT2' ELSE '' END + ' ' +
CASE WHEN Product3Active = 'T' THEN 'PRODUCT3' ELSE '' END))
FROM Table1

jackdewey
Starting Member

7 Posts

Posted - 2004-04-07 : 14:24:08

Actually, after fiddling with it a bit, I got something closer to what I am looking for with this:

SELECT Product1Act + Product2Act + Product3Act from (
SELECT Product1Act = CASE WHEN Product1Active = 'T' THEN 'PRODUCT1' ELSE '' END,
Product2Act = CASE WHEN Product2Active = 'T' THEN 'PRODUCT2' ELSE '' END,
Product3Act = CASE WHEN Product3Active = 'T' THEN 'PRODUCT3' ELSE '' END
FROM Table1) CaseStatement

However, for some reason, there is a space returned by the case statements when the value is F. I would have expected '' no to have a length of 0.

jackdewey
Starting Member

7 Posts

Posted - 2004-04-07 : 14:26:48

Using NULL instead of '' corrects that.

SELECT Product1Act + Product2Act + Product3Act from (
SELECT Product1Act = CASE WHEN Product1Active = 'T' THEN 'PRODUCT1' ELSE NULL END,
Product2Act = CASE WHEN Product2Active = 'T' THEN 'PRODUCT2' ELSE NULL END,
Product3Act = CASE WHEN Product3Active = 'T' THEN 'PRODUCT3' ELSE NULL END
FROM Table1) CaseStatement

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-07 : 15:35:16

quote:
Originally posted by jackdewey

Using NULL instead of '' corrects that.

SELECT Product1Act + Product2Act + Product3Act from (
SELECT Product1Act = CASE WHEN Product1Active = 'T' THEN 'PRODUCT1' ELSE NULL END,
Product2Act = CASE WHEN Product2Active = 'T' THEN 'PRODUCT2' ELSE NULL END,
Product3Act = CASE WHEN Product3Active = 'T' THEN 'PRODUCT3' ELSE NULL END
FROM Table1) CaseStatement

The reason we used '' instead of NULL is because of a connection setting called SET_CONCAT_NULL_YIELDS_NULL which will return a NULL if one of the components of a concatenized string is null. I would suggest as a more flexible alternative to NULL use the '' then SELECT LTRIM(RTRIM(Product1Act + Product2Act + Product3Act)). This will work regardless of the setting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources