Pivot Function

Question

Post reply

Pivot Function

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
March 11, 2015 at 11:18 am

#298665

Hi
I've managed to use the pivot function using the code below which gives me the following output:
Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2
This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.
Any help would be greatly appreciated!!
BO
select
[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf
from #TempTable
group by [WeekBand], [Location_Code]) p
pivot (sum(CountOf)
for [WeekBand] in ([Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
))
as pvt

Viewing 8 posts - 1 through 7 (of 7 total)

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

logitestus SSCrazy Points: 2928 More actions · Answer 1

I don't suppose you can offer some sample data + table definition for #TempTable?

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 2

I'm not familiar with pivot tables per se but one of the following should work...

select

[Location_Code] AS 'Location Code'

,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4],

[Gt 0-1] + [Gt 1-2] + [Gt 2-3] + [Gt 3-4] AS LOCATION_TOTAL

from (select [WeekBand]

,[Location_Code]

, count(*) as CountOf

from #TempTable

group by [WeekBand], [Location_Code]) p

pivot (sum(CountOf)

for [WeekBand] in ([Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]

))

as pvt

select

[Location_Code] AS 'Location Code'

,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4],

[Gt 0-1] + [Gt 1-2] + [Gt 2-3] + [Gt 3-4] AS LOCATION_TOTAL

FROM

(

select

[Location_Code] AS 'Location Code'

,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4],

from (select [WeekBand]

,[Location_Code]

, count(*) as CountOf

from #TempTable

group by [WeekBand], [Location_Code]) p

pivot (sum(CountOf)

for [WeekBand] in ([Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]

))

as pvt

) D

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Does this work?

select

[Location_Code] AS 'Location Code'

,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]

,[Gt 0-1] + [Gt 1-2] + [Gt 2-3] + [Gt 3-4] AS Total

from ...

or

select 'Location Code', [Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4], [Gt 0-1] + [Gt 1-2] + [Gt 2-3] + [Gt 3-4] AS Total

from

(

select

[Location_Code] AS 'Location Code'

,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]

from (select [WeekBand] ...

))

as pvt

) as tbl

Alternatively, & I'm speculating somewhat, but I think another solution may be to use a ROLLUP extension to the GROUP BY clause & include that in the 'WeekBand in' list. Might require some experimentation.

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

Gazareth

One Orange Chip

Points: 27737

March 11, 2015 at 12:09 pm

#1783416

Ha, well great minds etc. etc. !

logitestus SSCrazy Points: 2928 More actions · Answer 5

Given the following table definition and sample data:

CREATE TABLE #TempTable(Location_Code VARCHAR(50), WeekBand VARCHAR(50))

INSERT INTO #TempTable

VALUES ('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),

('North','Gt 0-1'),('North','Gt 0-1'),('North','Gt 0-1'),('West','Gt 0-1'),('West','Gt 0-1'),('West','Gt 0-1'),('West','Gt 0-1'),

('West','Gt 0-1'),('West','Gt 0-1'),('South','Gt 0-1'),('South','Gt 0-1'),('South','Gt 0-1'),('South','Gt 0-1'),('North','Gt 2-3'),

('North','Gt 2-3'),('North','Gt 2-3'),('West','Gt 2-3'),('West','Gt 2-3'),('South','Gt 2-3'),('South','Gt 2-3'),('South','Gt 2-3'),

('South','Gt 2-3'),('South','Gt 2-3'),('South','Gt 2-3'),('South','Gt 2-3'),('South','Gt 2-3'),('West','Gt 1-2'),('West','Gt 1-2'),

('West','Gt 1-2'),('South','Gt 1-2'),('South','Gt 1-2'),('North','Gt 3-4'),('North','Gt 3-4'),('North','Gt 3-4'),('North','Gt 3-4'),

('North','Gt 3-4'),('South','Gt 3-4'),('South','Gt 3-4')

I have updated your query (no performance investigation done, so YMMV):

select [Location_Code] AS 'Location Code'

,ISNULL([Gt 0-1],0) AS [Gt 0-1], ISNULL([Gt 1-2],0) AS [Gt 1-2], ISNULL([Gt 2-3],0) AS [Gt 2-3], ISNULL([Gt 3-4],0) AS [Gt 3-4]

,SUM(ISNULL([Gt 0-1],0) + ISNULL([Gt 1-2],0) + ISNULL([Gt 2-3],0) + ISNULL([Gt 3-4],0)) as Totals

from

(

select [WeekBand],[Location_Code], count(*) as CountOf

from #TempTable

group by [WeekBand], [Location_Code]

) p

pivot (sum(CountOf) for [WeekBand] in ([Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4])) as pv

group by pv.Location_Code,pv.[Gt 0-1], pv.[Gt 1-2], pv.[Gt 2-3], pv.[Gt 3-4]

For the attached result

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Of course, you can do it with a Cross tab approach.

It's all explained in here with comparisons to the pivot operator:

Cross tabs Part 1[/url]

Cross tabs Part 2[/url]

SELECT

[Location_Code] AS 'Location Code'

,SUM(CASE WHEN [WeekBand] = 'Gt 0-1' THEN CountOf ELSE 0 END) AS [Gt 0-1]

,SUM(CASE WHEN [WeekBand] = 'Gt 1-2' THEN CountOf ELSE 0 END) AS [Gt 1-2]

,SUM(CASE WHEN [WeekBand] = 'Gt 2-3' THEN CountOf ELSE 0 END) AS [Gt 2-3]

,SUM(CASE WHEN [WeekBand] = 'Gt 3-4' THEN CountOf ELSE 0 END) AS [Gt 3-4]

,SUM(CountOf) AS [LOCATION_TOTAL]

FROM (SELECT [WeekBand]

,[Location_Code]

, count(*) as CountOf

FROM #TempTable

GROUP BY [WeekBand], [Location_Code]) p

GROUP BY [Location_Code];

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 7

Cheers guys - all really, really helpful.

I'm not sure which method I'll use - going to play around a bit with your suggestions.

Thanks again.