2

I'm pretty experienced in C#, but still mostly a beginner in SQL.
We have an application in C#, using an MSSQL database.
One part of our application is simply a list of pre-written queries as reports that the application shows by simply running the query and sticking the returned table into a gridView. The user is requesting a new report that I'm not entirely sure is even possible with just SQL and is going to require manipulation of the returned data to get it to look right.

The data that the users want, in the way they want it presented would require me to be able to take this table:

Date  Category  Count
---------------------
date1 Cat1        x1
date1 Cat2        y1
...
date1 CatN        z1

gotten from this query: 

select Date, Category, COUNT(*) as 'Count' 
from Table 
group by Date, Category
turned into this table: 
Date  Cat1  Cat2  ...  CatN
---------------------------
date1 x1    y1    ...  z1
date2 x2    y2    ...  z2

so that I can join it by date to the other half of the data they want.

Long-winded explanation, short question and followup: is this possible to do, no matter how painfully, with just SQL?
If so, how?

Zind
  • 751
  • 1
  • 7
  • 17

1 Answers1

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
Donnie
  • 45,732
  • 10
  • 64
  • 86
  • Thanks for the example, it was quite helpful, and I learned something new! :) Pivot works for about 2/3 of our cases, since they have standard categories - however, for the rest of them they are dynamic, so this is a no-go. BUT, we're able to get them all the data they want by using WITH ROLLUP. It's not as pretty as we'd have liked, but it definitely works. – Zind Jul 16 '10 at 11:28