Alternatives to Pivot - 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

General SQL Server Forums

New to SQL Server Programming

Alternatives to Pivot

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-05-20 : 22:46:13

Hi All

Just wondering if anyone can help me out regarding this Pivot function issue. I'm working on an SQL statement and was successful in using the Pivot table function to display the data I require. However Pivot is not compatible with this particular driver and I would like to know if there is an alternative function that would provide the same result

SELECT * FROM ( SELECT DATENAME(DW, [IncDate]) AS WEEKDAY
FROM irIncident AS I INNER JOIN pdEmployee AS E ON I.EmpKey = E.EmpKey ";
WHERE (E.Department = '1000' ) AND (I.HlthCare = 'Y') AND
(I.IncDate >= 1/1/1996) AND (I.IncDate <= 1/1/2010))
AS SourceTable PIVOT (COUNT(WEEKDAY) FOR WEEKDAY IN ([Sunday], [Monday],
"[Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])) AS PivotTable

This displays a table that looks like this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 23:46:48

i would suggest case when . . but i saw your the other thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144938

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 23:56:10

you can also do this .. but will not be efficient compare to the PIVOT or CASE . . WHEN method


select *
from   (
           select count(*) as cnt
           from   . . .
           where  datename(dw, [IncDate]) = 'Monday'
       ) as [mon]
cross join
       (
           select count(*) as cnt
           from   . . .
           where  datename(dw, [IncDate]) = 'Tuesday'
       ) as [tue]

KH
[spoiler]Time is always against us[/spoiler]

rc1138
Starting Member

35 Posts

Posted - 2010-05-21 : 00:35:25

Awesome Khtan this is great
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources