column totals for pivot query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

column totals for pivot query

Author

Topic

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-10 : 07:24:40

Can anyone help me with this..

I need to add column total to my pivot query..
here's the query :

Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other]

From

(

SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total

FROM Cetus.dbo.JMPackSlipHeader as JMPSH

INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID

INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID

Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'

and C.MerchPreference = 'Y'

GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)

) AS S

PIVOT

(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate

I need results to look like this:

Packaging Date Bag BoxAA EmptyStand FullStand Other
2009/05/04 4 38 8 4 NULL
2009/05/05 17 175 10 NULL NULL
2009/05/06 15 442 4 16 NULL
2009/05/07 6 561 10 5 NULL
2009/05/08 8 372 12 NULL NULL
2009/05/11 4 142 3 1 NULL
2009/05/12 16 151 21 11 NULL
Total 70 1881 68 37 0

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-07-10 : 07:30:55

you have posted the same in new to sql server forum.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128569

Karthik

ntswakisto
Starting Member

33 Posts

Posted - 2009-07-10 : 08:07:01

quote:
Originally posted by karthik_padbanaban

you have posted the same in new to sql server forum.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128569

Karthik

Yes...i made a mistake it was suppose to be posted...here under t-Sql 2005....i also made changes i only need column total now...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources