Home > DeveloperSection > Forums > SQL Server: GROUP BY Aggregation semantics with the PIVOT operator
marcel ethan
marcel ethan

Total Post:105

Points:735
Posted on    May-08-2013 1:27 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1246  View(s)
Rate this:
Hi Everyone!

I am on SQL Server 2008 and I have a table containing WA metrics of the following form :

CREATE TABLE #VistitorStat
(
 datelow datetime,
 datehigh datetime,
 name varchar(255),
 cnt  int
)
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.

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-09-2013 2:32 AM

Hi Marcel!

I m not sure of the result you want but this gives a line per day:

CREATE TABLE #VistitorStat
(
 datelow datetime,
 datehigh datetime,
 name varchar(255),
 cnt  int
)

insert into #VistitorStat
      select  '2009-07-25 00:00:00.000','2009-07-26 00:00:00.000',   'New Visitor',       221 
union select  '2009-07-25 00:00:00.000',' 2009-07-26 00:00:00.000',   'Unique Visitors',   225
union select  '2009-07-25 00:00:00.000',' 2009-07-26 00:00:00.000',   'Return Visitors',   0
union select  '2009-07-25 00:00:00.000',' 2009-07-26 00:00:00.000',   'Repeat Visitors',   22
union select  '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000',   'New Visitor'    ,   263
union select  '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000',   'Unique Visitors',   269
union select  '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000',   'Return Visitors',   4
union select  '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000',   'Repeat Visitors',   38

select * from #VistitorStat 
pivot (
    sum(cnt)
    for name in ([New Visitor],[Unique Visitors],[Return Visitors], [Repeat Visitors])


I hope it is helpful for you!

Don't want to miss updates? Please click the below button!

Follow MindStick