Regrading transpose from rows to columns and indicator 0 and 1 and sum combinations

1

I have column like
id date
1001 1/7/2010
1001 2/8/2013
1001 3/9/2014
2001 1/9/2009
2001 1/10/1010

For each id create a marker for each year 2010-2016
with 1=yes ,data available
0=no data available
count the number of ids with each combination of values
2010 2011 2012 2013 2014 2015 2016 id count
1 1 1 1 1 1 1 100000
1 1 1 1 1 1 0 5000
to transpose and give marker and then count

2

How will you do this step?

3 
SELECT *, [2010] + [2011] + [2012] + [2013] + [2014] + [2015] + [2016] AS count
FROM (
    SELECT
        MAX(CASE WHEN date >= '20100101' AND date < '20110101' THEN 1 ELSE 0 END) AS [2010],
        MAX(CASE WHEN date >= '20110101' AND date < '20120101' THEN 1 ELSE 0 END) AS [2011],
        MAX(CASE WHEN date >= '20120101' AND date < '20130101' THEN 1 ELSE 0 END) AS [2012],
        MAX(CASE WHEN date >= '20130101' AND date < '20140101' THEN 1 ELSE 0 END) AS [2013],
        MAX(CASE WHEN date >= '20140101' AND date < '20150101' THEN 1 ELSE 0 END) AS [2014],
        MAX(CASE WHEN date >= '20150101' AND date < '20160101' THEN 1 ELSE 0 END) AS [2015],
        MAX(CASE WHEN date >= '20160101' AND date < '20170101' THEN 1 ELSE 0 END) AS [2016],
        id
    FROM table_name
    GROUP BY id
) AS derived
1 Like
4

Thanks

5

Thanks for that.
How would I get sum of all this combinations.
1111111
111110
111101
like this sum of combinations i should get as 128

6

how is that 128? If I add these up as binary numbers I get 250