I am on SQL Server 2008 and I have a table containing WA metrics of the following form :
CREATE TABLE #VistitorStat
Two days worth of data in the table looks like so:
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 New Visitor 221
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Unique Visitors 225
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Return Visitors 0
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Repeat Visitors 22
2009-07-26 00:00:00.000 2009-07-27 00:00:00.000 New Visitor 263
2009-07-26 00:00:00.000 2009-07-27 00:00:00.000 Unique Visitors 269
2009-07-26 00:00:00.000 2009-07-27 00:00:00.000 Return Visitors 4
2009-07-26 00:00:00.000 2009-07-27 00:00:00.000 Repeat Visitors 38
I want to group by the days and pivot the metrics into row form. The examples for using the PIVOT operator that I can find only show aggregation based on the SUM and
MAX aggregate function. Presumably I need to convey GROUP BY semantics to the PIVOT operator -- note: I can't find any clear examples/ documentation on how to
achieve this. Could someone please post the correct syntax of this -- with the use of the PIVOT operator -- of this query.
If this is not possible with pivot -- can you come up with an elegant way of writing the query ? If not i'll just have to generate the data in transposed form.
-- post answer edit --
I have come to the conclusion that the pivot operator is unrelenting (so far so that I consider it a syntax hack) -- I have solved the problem by generating the data
in a transposed fashion. I welcome comments.