Amol Patil

With SQL Server 2005 a lot of new features have been introduced. One of these new features is PIVOT .  What this allows you to do is to turn query results on their side, so instead of having results listed down like the listing below, you have results listed across. SalesPerson Product SalesAmount Bob Pickles $100.00 Sue Oranges $50.00 Bob Pickles $25.00 Bob Oranges $300.00 Sue Oranges $500.00 With a straight query the query results would be listed down, but the ideal solution would be to list the Products across the top for each SalesPerson, such as the following: SalesPerson Oranges Pickles Bob $300.00 $125.00 Sue $550.00   To use PIVOT you need to understand the data and how you want the data displayed.  First you have the data rows, such as SalesPerson and the columns, such as the Products and then the values to display for each cross section.  Here is a simple query that allows us to pull the cross-tab results. ...

I have a table "sampledata"  with data like id    city 1     Pune 2     Banglore 3    Mumbai 4    Nagpur ------------------------------------ DECLARE @list nvarchar(max) SELECT @list = COALESCE(@list + ', ', '') + CAST(city AS varchar(100)) FROM sampledata SELECT @list as List -----------------------Output----------------  Pune, Banglore, Mumbai, Nagpur

In this article we will go over basic understanding of Rollup clause in SQL Server. ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. Let us understand how it works by using an example. Consider a table with the following structure and data: CREATE TABLE tblPopulation ( Country VARCHAR ( 100 ), [State] VARCHAR ( 100 ), City VARCHAR ( 100 ), [Population (in Millions)] INT ) GO INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'East Delhi' , 9 ) INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'South Delhi' , 8 ) INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'North Delhi' , 5.5 ) INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'West Delhi' , 7.5 ) INSERT INTO tblPopulation VALUES ( 'India' , 'Karnataka' , 'Bangalore' , 9.5 ) INSERT INTO tblPopulation VALUES ( 'India' , 'Karnataka'...

  I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below, 1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem) 2: FROM Orders GROUP BY CustomerName,CustomerName Fig – (1) Group By clause.      Which will returns the result as shown below, Fig – (2) Result of GROUP BY clause     However what if you want result as display below, Fig – (3) Desire result        Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP.  ROLLUP adds new row for each column used in GROUP BY clause. First have a look ...