SQL pivot help

Forums › IT Pro and developers › SQL pivot help

s.joseph

587 posts

Ultimate Geek

Trusted

#87906 10-Aug-2011 11:03

hey trying to rename a header on the pivot,
The month is stored as int type, can i convert it to month name (jan, feb, march) across the top of the pivoted table instead of numbers?

how would i go about this?

Example:
SELECT*

FROM Table1

PIVOT(

MAX(Score)

FOR[Month]IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

AS p

Thanks!

bazzer

3438 posts

Uber Geek

Trusted

#505044 11-Aug-2011 15:33

Is this homework? If not, I'll help you out.

s.joseph

587 posts

Ultimate Geek

Trusted

#505057 11-Aug-2011 15:57

it is homework :) figured it out now using 'AS'

thanks!

bazzer

3438 posts

Uber Geek

Trusted

#505078 11-Aug-2011 16:22

Why don't you post your solution then someone else can learn from you too! :)

bazzer

3438 posts

Uber Geek

Trusted

#505101 11-Aug-2011 17:06

P.S. Not sure I really know what you mean by using 'AS'. I'd probably go with something like:

SELECT *
FROM (SELECT DateName(Month, DateAdd(Month, [Month], 0) - 1) as [MonthName], Score FROM Table_1) AS SourceTable
PIVOT (Max(Score) FOR [MonthName] IN ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])) AS PivotTable

but keen to see other approaches.

s.joseph

587 posts

Ultimate Geek

Trusted

#505805 12-Aug-2011 22:39

turns out i havnt got it sorted haha, your method didn't work above as i have it saved as a varchar not date type.

The method i was trying was to use was
SELECT Month [1] As Jan, [2] as Feb etc

but it didn't work as there not columns till after being pivoted, so i get a non existent column error.

nzkc

1572 posts

Uber Geek

#505821 12-Aug-2011 23:18

As its homework I don't want to do it completely for you...plus you haven't said which DBMS this is for but...

cough derived tables cough common table expressions cough

s.joseph

587 posts

Ultimate Geek

Trusted

#507031 15-Aug-2011 21:48

i did it finally !!! yay!!

SELECT*

FROM (

SELECT

UserID,

caseMonth

When 1 then 'Jan'

WHEN 2 THEN 'Feb'

WHEN 3 THEN 'March'

WHEN 4 THEN 'April'

WHEN 5 THEN 'May'

WHEN 6 THEN 'June'

WHEN 7 THEN 'July'

WHEN 8 THEN 'Aug'

WHEN 9 THEN 'Sept'

WHEN 10 THEN 'Oct'

WHEN 11 THEN 'Nov'

WHEN 12 THEN 'Dec'

END as [Month],

Score

From dbo.Table1

) temp

PIVOT(

MAX(Score)

FOR[Month]IN(

[Jan],[Feb],[March],[April],[May],[June],[July],[Aug],[Sept],[Oct],[Nov],[Dec]

)

)PivotTable