CODEWITHSUNDEEP
sqlsql-server-2008
Zind
Zind

Reputation: 761

SQL row value as column name

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?

Upvotes: 2

Views: 42501

Answers (1)

Donnie
Donnie

Reputation: 46943

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

Upvotes: 3

Related Questions