How to have one row multiple columns instead of multiple rows

Question

I have the following data:

In SQL Server How can I have group by weekdate so I have only one row for each weekdate, example for the weekdate 2015-11-14:

Any clue?

Answer 1

Use conditional aggregation.

select cast(weekdate as date), 
sum(case when permittype = 0 then total else 0 end) as permittype0,
sum(case when permittype = 1 then total else 0 end) as permittype1
from tablename
group by cast(weekdate as date)

Answer 2

I would do this using conditional aggregation:

select weekdate,
       sum(case when permittype = 0 then total else 0 end) as permitttype0,
       sum(case when permittype = 1 then total else 0 end) as permitttype1
from followingdata t
group by weekdate
order by weekdate;

You can also use pivot syntax, if you prefer.