pivot query

Question

Post reply

pivot query

sayedkhalid99

Ten Centuries

Points: 1394
More actions
September 22, 2010 at 3:24 am

#240675

hi all,
i am new to t-sql i need to sum of the sales based on months and quarters together can some one help me to solve this.
CREATE TABLE [dbo].[tablesales2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost] [numeric](18, 0) NULL,
[saledate] [datetime] NULL,
)
insert into [tablesales2](name,cost,saledate)values('pen',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pen',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pen',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pen',400,'12/12/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',400,'12/12/2001')
i need to have the output
NameJan FebMarAprMayJunJulAugSepOctNovDecQ1Q2Q3 Q4 Total
pen10020003000000000400300300 400 1000
pencil100200030000000004003003004001000
regards,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Syed Khalid, tel me, honestly, it is an interview question or home-work question , isn't it?

sayedkhalid99 Ten Centuries Points: 1394 More actions · Answer 2

hi,

neither interview nor homework it is just a requirement.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

SELECT Name,

SUM(CASE WHEN DATEPART(month,saledate)=1 THEN cost ELSE 0 END) AS Jan,

SUM(CASE WHEN DATEPART(month,saledate)=2 THEN cost ELSE 0 END) AS Feb,

SUM(CASE WHEN DATEPART(month,saledate)=3 THEN cost ELSE 0 END) AS Mar,

SUM(CASE WHEN DATEPART(month,saledate)=4 THEN cost ELSE 0 END) AS Apr,

SUM(CASE WHEN DATEPART(month,saledate)=5 THEN cost ELSE 0 END) AS May,

SUM(CASE WHEN DATEPART(month,saledate)=6 THEN cost ELSE 0 END) AS Jun,

SUM(CASE WHEN DATEPART(month,saledate)=7 THEN cost ELSE 0 END) AS Jul,

SUM(CASE WHEN DATEPART(month,saledate)=8 THEN cost ELSE 0 END) AS Aug,

SUM(CASE WHEN DATEPART(month,saledate)=9 THEN cost ELSE 0 END) AS Sep,

SUM(CASE WHEN DATEPART(month,saledate)=10 THEN cost ELSE 0 END) AS Oct,

SUM(CASE WHEN DATEPART(month,saledate)=11 THEN cost ELSE 0 END) AS Nov,

SUM(CASE WHEN DATEPART(month,saledate)=12 THEN cost ELSE 0 END) AS Dec,

SUM(CASE WHEN DATEPART(quarter,saledate)=1 THEN cost ELSE 0 END) AS Q1,

SUM(CASE WHEN DATEPART(quarter,saledate)=2 THEN cost ELSE 0 END) AS Q2,

SUM(CASE WHEN DATEPART(quarter,saledate)=3 THEN cost ELSE 0 END) AS Q3,

SUM(CASE WHEN DATEPART(quarter,saledate)=4 THEN cost ELSE 0 END) AS Q4,

SUM(cost) AS Total

FROM dbo.tablesales2

GROUP BY Name

ORDER BY Name;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Hmmm.. try this:

; with cte as

(

SELECT name,cost , saledate ,

Quarter_Num = DATEPART(M ,[saledate] )

,Month_Name = LEFT ( DATENAME( M , [saledate]) , 3 )

FROM [dbo].[tablesales2]

)

SELECT

Name,

SUM ( CASE WHEN Month_Name = 'Jan' THEN Cost ELSE 0 END ) AS 'JAN' ,

SUM ( CASE WHEN Month_Name = 'Feb' THEN Cost ELSE 0 END ) AS 'FEB' ,

SUM ( CASE WHEN Month_Name = 'Mar' THEN Cost ELSE 0 END ) AS 'MAR' ,

SUM ( CASE WHEN Month_Name = 'Apr' THEN Cost ELSE 0 END ) AS 'APR' ,

SUM ( CASE WHEN Month_Name = 'May' THEN Cost ELSE 0 END ) AS 'MAY' ,

SUM ( CASE WHEN Month_Name = 'Jun' THEN Cost ELSE 0 END ) AS 'JUN' ,

SUM ( CASE WHEN Month_Name = 'Jul' THEN Cost ELSE 0 END ) AS 'JUL' ,

SUM ( CASE WHEN Month_Name = 'Aug' THEN Cost ELSE 0 END ) AS 'AUG' ,

SUM ( CASE WHEN Month_Name = 'Sep' THEN Cost ELSE 0 END ) AS 'SEP' ,

SUM ( CASE WHEN Month_Name = 'Oct' THEN Cost ELSE 0 END ) AS 'OCT' ,

SUM ( CASE WHEN Month_Name = 'Nov' THEN Cost ELSE 0 END ) AS 'NOV' ,

SUM ( CASE WHEN Month_Name = 'Dec' THEN Cost ELSE 0 END ) AS 'DEC' ,

SUM ( CASE WHEN Quarter_Num BETWEEN 1 AND 3 THEN Cost ELSE 0 END ) AS 'Q1' ,

SUM ( CASE WHEN Quarter_Num BETWEEN 4 AND 6 THEN Cost ELSE 0 END ) AS 'Q2' ,

SUM ( CASE WHEN Quarter_Num BETWEEN 7 AND 9 THEN Cost ELSE 0 END ) AS 'Q3' ,

SUM ( CASE WHEN Quarter_Num BETWEEN 10 AND 12 THEN Cost ELSE 0 END ) AS 'Q4',

SUM ( Cost ) AS Total

FROM

cte

GROUP BY

Name

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

OOooch :pinch:, Mark beat me to it..

one variation between Mark and mine, i used DATENAME for the Month, he used DATEPART.. both do the same, infact, mark's is good one, considering the fact that we don't need that extra LEFT(,3) thingy 😉

sayedkhalid99 Ten Centuries Points: 1394 More actions · Answer 6

thanks a lot both of you for your support , both solution works.

sayedkhalid99 Ten Centuries Points: 1394 More actions · Answer 7

one thing more is it possible to add total also at the bottom for each months and quarters , i tried to use union didn't work , do i need to add the results to temporary table then i have to aggregate the columns?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

Try this:

Tweaked with the idea from Mark:

; with cte0 as

(

SELECT name,cost , saledate

,Quarter_Num = DATEPART(Q ,[saledate] )

,Month_Num = DATEPART(M ,[saledate] )

FROM [dbo].[tablesales2]

) -- SELECT * FROM cte0

, cte1 as

(

SELECT

Name,

SUM ( CASE WHEN Month_Num = 1 THEN Cost ELSE 0 END ) AS 'JAN' ,

SUM ( CASE WHEN Month_Num = 2 THEN Cost ELSE 0 END ) AS 'FEB' ,

SUM ( CASE WHEN Month_Num = 3 THEN Cost ELSE 0 END ) AS 'MAR' ,

SUM ( CASE WHEN Month_Num = 4 THEN Cost ELSE 0 END ) AS 'APR' ,

SUM ( CASE WHEN Month_Num = 5 THEN Cost ELSE 0 END ) AS 'MAY' ,

SUM ( CASE WHEN Month_Num = 6 THEN Cost ELSE 0 END ) AS 'JUN' ,

SUM ( CASE WHEN Month_Num = 7 THEN Cost ELSE 0 END ) AS 'JUL' ,

SUM ( CASE WHEN Month_Num = 8 THEN Cost ELSE 0 END ) AS 'AUG' ,

SUM ( CASE WHEN Month_Num = 9 THEN Cost ELSE 0 END ) AS 'SEP' ,

SUM ( CASE WHEN Month_Num = 10 THEN Cost ELSE 0 END ) AS 'OCT' ,

SUM ( CASE WHEN Month_Num = 11 THEN Cost ELSE 0 END ) AS 'NOV' ,

SUM ( CASE WHEN Month_Num = 12 THEN Cost ELSE 0 END ) AS 'DEC' ,

SUM ( CASE WHEN Quarter_Num = 1 THEN Cost ELSE 0 END ) AS 'Q1' ,

SUM ( CASE WHEN Quarter_Num = 2 THEN Cost ELSE 0 END ) AS 'Q2' ,

SUM ( CASE WHEN Quarter_Num = 3 THEN Cost ELSE 0 END ) AS 'Q3' ,

SUM ( CASE WHEN Quarter_Num = 4 THEN Cost ELSE 0 END ) AS 'Q4',

SUM ( Cost ) AS Total

FROM

cte0

GROUP BY

Name

)

SELECT Name

,JAN ,FEB ,MAR

,APR ,MAY ,JUN

,JUL ,AUG ,SEP

,OCT ,NOV ,DEC

,Q1, Q2, Q3, Q4

,Total

FROM

cte1

UNION ALL

SELECT 'Grand Total'

, SUM (JAN ), SUM (FEB ), SUM (MAR )

, SUM (APR ), SUM (MAY ), SUM (JUN )

, SUM (JUL ), SUM (AUG ), SUM (SEP )

, SUM (OCT ), SUM (NOV ), SUM (DEC)

, SUM ( Q1 ), SUM ( Q2 ), SUM ( Q3 ), SUM ( Q4 )

, SUM (Total)

FROM cte1

sayedkhalid99 Ten Centuries Points: 1394 More actions · Answer 9

wow thanks alot ... now i am learning the power of cte...

drew.allen SSC Guru Points: 76985 More actions · Answer 10

sayedkhalid99 (9/22/2010)
one thing more is it possible to add total also at the bottom for each months and quarters , i tried to use union didn't work , do i need to add the results to temporary table then i have to aggregate the columns?

It's probably most efficient to use the WITH ROLLUP clause, but I'm not at a computer where I can test it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76985 More actions · Answer 11

I finally had a chance to test this on a table with 18K records. Here are the results for producing a grand total:

WITH ROLLUP

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LEAD'. Scan count 1, logical reads 2428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 93 ms, elapsed time = 108 ms.

SQL Server parse and compile time:

CPU time = 63 ms, elapsed time = 63 ms.

Warning: Null value is eliminated by an aggregate or other SET operation.

UNION ALL

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'LEAD'. Scan count 2, logical reads 4856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 209 ms.

As you can see, the UNION ALL has exactly double the number of Scan Counts and Logical Reads of the WITH ROLLUP.

Here is the query I used with the rollup.

SELECT CASE Grouping(Name) WHEN 1 THEN 'Total' ELSE Name END AS [Name],