---
title: "SQL Server: GROUP BY Aggregation semantics with the PIVOT operator"  
description: "SQL Server: GROUP BY Aggregation semantics with the PIVOT operator"  
author: "marcel ethan"  
published: 2013-05-08  
updated: 2013-05-09  
canonical: https://www.mindstick.com/forum/839/sql-server-group-by-aggregation-semantics-with-the-pivot-operator  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# SQL Server: GROUP BY Aggregation semantics with the PIVOT operator

Hi Everyone!\
I am on [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) 2008 and I have a table containing WA metrics of the following form :\
[CREATE TABLE](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) #VistitorStat( datelow [datetime](https://www.mindstick.com/forum/12949/how-to-validate-if-a-datetime-field-is-not-null-empty), datehigh datetime, name varchar(255), cnt int)Two days worth of [data in the table](https://www.mindstick.com/forum/34030/how-to-display-data-in-the-table-using-angular-js) looks like so:\
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 New Visitor 2212009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Unique Visitors 2252009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Return Visitors 02009-07-25 00:00:00.000 2009-07-26 00:00:00.000 Repeat Visitors 222009-07-26 00:00:00.000 2009-07-27 00:00:00.000 New Visitor 2632009-07-26 00:00:00.000 2009-07-27 00:00:00.000 Unique Visitors 2692009-07-26 00:00:00.000 2009-07-27 00:00:00.000 Return Visitors 42009-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](https://www.mindstick.com/forum/34329/how-i-can-use-pivot-operator) that I can find only show [aggregation](https://www.mindstick.com/forum/217/what-is-aggregation-and-how-it-maps-into-a-java-class) based on the SUM and \
MAX [aggregate](https://www.mindstick.com/blog/52/aggregate-functions-in-database) function. Presumably I need to convey GROUP BY semantics to the PIVOT operator -- note: I can't find any clear examples/ [documentation](https://answers.mindstick.com/qa/30462/what-is-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](https://www.mindstick.com/articles/12997/5-essential-tips-on-resume-writing-for-business-analysts) 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!

## Replies

### Reply by AVADHESH PATEL

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', 225union select '2009-07-25 00:00:00.000',' 2009-07-26 00:00:00.000', 'Return Visitors', 0union select '2009-07-25 00:00:00.000',' 2009-07-26 00:00:00.000', 'Repeat Visitors', 22union select '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000', 'New Visitor' , 263union select '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000', 'Unique Visitors', 269union select '2009-07-26 00:00:00.000',' 2009-07-27 00:00:00.000', 'Return Visitors', 4union 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!


---

Original Source: https://www.mindstick.com/forum/839/sql-server-group-by-aggregation-semantics-with-the-pivot-operator

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.