Q:

SQL Server add strings (of 0,1) logically

I have a table (lets call it AAA) in which each of my 4 channel_ids has its own dedicated publish flag column which is a char(4) consisting of 0s,1s
Here is an example
on_date                 rate_id type_id channel_id  publish_flag
2016-02-08 00:00:00.000 98      1       1           1000
2016-02-08 00:00:00.000 98      1       2           0100
2016-02-08 00:00:00.000 98      1       4           0000
2016-02-08 00:00:00.000 98      2       1           1000
2016-02-08 00:00:00.000 98      2       2           0100
2016-02-08 00:00:00.000 98      2       3           0001
2016-02-08 00:00:00.000 98      2       4           0000

I want to group the results by 

on_date (in this example 2016-02-08 00:00:00.000 98)
rate_id (in this example 98)
type_id (in this example 1 and 2) 
and instead of channel_id to have the "logical addition" of the publish flag columns

Example of the above transformation:
on_date                 rate_id type_id     publish_flag
2016-02-08 00:00:00.000 98      1           1100    
2016-02-08 00:00:00.000 98      2           1101

So for first group:
1000   +
0100   +
0000   =
--------
1100

and for the second group:
1000   +
0100   +
0001   +
0000   =
--------
1101

Is there a trick in which I can achieve the above in MS SQL Server 2008R2?
Thank you

A:

As far as i can tell, you need the highest bit for each position.
Try this:
SELECT 
  on_date,rate_id,type_id,
  MAX(substring(publish_flag, 1,1)) + MAX(substring(publish_flag, 2,1)) + 
  MAX(substring(publish_flag, 3,1)) + MAX(substring(publish_flag, 4,1)) as publish_flag
FROM YourTable
GROUP BY on_date,rate_id,type_id

