SQL row value as column name

Answer 1

What you're looking for is Pivot

Answer 2

You'll want to look at the PIVOT operation:

http://www.tsqltutorials.com/pivot.php

Answer 3

You need to use pivot. One issue that may give you problems is that you must know how many categories you have since a query can't ever return a dynamic number of columns (not even one that you're pivoting).

I don't have your schema, but this should be about right (may take a few touchups here and there, but it should get you started)...

select
  Date, [1] as Cat1, [2] as Cat2, [3] as Cat3, ...
from
  (select date, category from table) p
pivot (
  count(*)
for
  Category in ([1],[2],[3],...)
) pivoted
order by
  pivoted.date