CODEWITHSUNDEEP
sqlsql-serverconditional-aggregation
VAAA
VAAA

Reputation: 15039

How to have one row multiple columns instead of multiple rows

I have the following data:

enter image description here

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:

enter image description here

Any clue?

Upvotes: 0

Views: 269

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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)

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 1

Related Questions