Something like a pivot chart - 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

Something like a pivot chart

Author

Topic

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-12 : 23:10:39

Hi,

Thanks a lot for answering my previous questions.
Here's one more thing that I need. I have my data in one table called as Team_Aggregate.
The fields there are TeamID, Date (1st of every month), Permanent Employeed, Contractor, Open Positions, Total Employees, Max Posns.

Now, when a user selects some months data that he wants to display I want it to appear something like this -

Columns as Month Names (Jan 08 Feb 08...June 08)
Rows as Team Name
and each cell = Team Total for that month and the corresponding team.
Can I do something like this in sql? I want to display the data on the web eventually where I am using Gridview with datasource and databind methods.

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 23:55:24

something like this:-

SELECT TeamName,
SUM(CASE WHEN MONTH(ta.Date)=1 AND YEAR(ta.Date)=2008 THEN ta.TotalEmployees ELSE 0 END)AS [Jan 08],
SUM(CASE WHEN MONTH(ta.Date)=2 AND YEAR(ta.Date)=2008 THEN ta.TotalEmployees ELSE 0 END)AS [Feb 08],
SUM(CASE WHEN MONTH(ta.Date)=3 AND YEAR(ta.Date)=2008 THEN ta.TotalEmployees ELSE 0 END)AS [Mar 08],
SUM(CASE WHEN MONTH(ta.Date)=4 AND YEAR(ta.Date)=2008 THEN ta.TotalEmployees ELSE 0 END)AS [Apr 08],
...
SUM(CASE WHEN MONTH(ta.Date)=12 AND YEAR(ta.Date)=2008 THEN ta.TotalEmployees ELSE 0 END)AS [Dec 08]
FROM Team_Aggregate ta
INNER JOIN Team t
ON t.TeamID=ta.TeamID
GROUP BY t.TeamName

or if you're using sql 2005 you can use PIVOT operator also

SELECT * 
FROM
(SELECT t.TeamName,ta.TotalEmployees,
left(datename(mm,t.Date),3)+ ' '+right(year(t.Date),2) AS Period  FROM Team_Aggregate ta
INNER JOIN Team t
ON t.TeamID=ta.TeamID)m
PIVOT (SUM(m.TotalEmployees) FOR m.Period IN ([Jan 08],[Feb 08],...,[Dec 08]))p

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 00:49:24

Hi,

I didn't quite get that one but I will learn it although I guess that you are adding the Team_Total for all the months which isn't what I want to do.
I want the month names to appear in the column headings like June 08 July 08 etc.
In the rows I want TeamNames and corresponding team totals. All the team totals are stored in the Team_Agg table as under -

TeamName Date TeamTotal
Team1 01/06/2008 12
Team2 01/07/2008 13
etc etc
and I want a summary like

June 08 July 08 August 08 ...
Team1 12 12 13
Team2 9 11 10
Team3 13 13 12

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 01:28:09

quote:
Originally posted by avkuvalekar

Hi,

I didn't quite get that one but I will learn it although I guess that you are adding the Team_Total for all the months which isn't what I want to do.
I want the month names to appear in the column headings like June 08 July 08 etc.
In the rows I want TeamNames and corresponding team totals. All the team totals are stored in the Team_Agg table as under -

TeamName Date TeamTotal
Team1 01/06/2008 12
Team2 01/07/2008 13
etc etc
and I want a summary like

June 08 July 08 August 08 ...
Team1 12 12 13
Team2 9 11 10
Team3 13 13 12

Thanks

Thats exactly what the posted code does. It just groups by teamname first and then based on month names returns the TeamTotal itelf as new columns.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 01:54:12

I tried this

quote:

select * from (select dbo.uview_DimHC_Team_Details_view1.TeamName, dbo.uview_DimHC_Team_Details_view1.Team_Total,
left(datename(mm,Team_Date),3) + ' ' + right(year(Team_Date),2) as PERIOD from dbo.DimHC_Team_Agg
PIVOT ( SUM (Team_Total) for PERIOD IN ([Jun 08] , [Jul 08])))

I would guess that INNER JOIN won't be required here because all my information is stored in this view - dbo.uview_DimHC_Team_Details_view1

I got the following error - Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Any idea why?

Thanks.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 01:56:06

Or this one too -

quote:

select * from (select dbo.uview_DimHC_Team_Details_view1.TeamName, dbo.uview_DimHC_Team_Details_view1.Team_Total,
left(datename(mm,Team_Date),3) + ' ' + right(year(Team_Date),2) as PERIOD from dbo.uview_DimHC_Team_Details_view1
PIVOT ( SUM (Team_Total) for PERIOD IN ([Jun 08] , [Jul 08])))

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 02:25:35

This worked btw -

select * from (select dbo.uview_DimHC_Team_Details_view1.TeamName, dbo.uview_DimHC_Team_Details_view1.Team_Total,
left(datename(mm,Team_Date),3) + ' ' + right(year(Team_Date),2) as PERIOD from dbo.uview_DimHC_Team_Details_view1) te
PIVOT ( sum(Team_Total) for PERIOD IN ([Jun 08] , [Jul 08], [Aug 08])) p

:)

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 05:07:57

but one more thing. :)
I want to display the whole year's data but as I have created teh database just now the data for months like April isn't there and the column is NULL. How can I omit it then?

Like,

select * from (select dbo.uview_DimHC_Team_Details_view1.TeamName, dbo.uview_DimHC_Team_Details_view1.Team_Total,
left(datename(mm,Team_Date),3) + ' ' + right(year(Team_Date),2) as PERIOD from dbo.uview_DimHC_Team_Details_view1) te
PIVOT ( sum(Team_Total) for PERIOD IN ([Apr 08], [May 08], [Jun 08] , [Jul 08], [Aug 08])) p

a query like this shouldn't return the Apr 08 column because it doesn't exist.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 05:46:56

[code]SELECT TeamName,
SUM(Team_Total) AS [Tot 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 1 THEN Team_Total ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 2 THEN Team_Total ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 3 THEN Team_Total ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 4 THEN Team_Total ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 5 THEN Team_Total ELSE 0 END) AS [May 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 6 THEN Team_Total ELSE 0 END) AS [Jun 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 7 THEN Team_Total ELSE 0 END) AS [Jul 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 8 THEN Team_Total ELSE 0 END) AS [Aug 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 9 THEN Team_Total ELSE 0 END) AS [Sep 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 10 THEN Team_Total ELSE 0 END) AS [Oct 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 11 THEN Team_Total ELSE 0 END) AS [Nov 08],
SUM(CASE WHEN DATEPART(MONTH, Team_Date) = 12 THEN Team_Total ELSE 0 END) AS [Dec 08],
FROM dbo.uView_DimHC_Team_Details_View1
WHERE Team_Date >= '20080101'
AND Team_Date < '20090101'
GROUP BY TeamName
ORDER BY TeamName[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 05:58:49

quote:
Originally posted by avkuvalekar

a query like this shouldn't return the Apr 08 column because it doesn't exist.

Always return complete resultset to your grid and then hide columns you don't want there!

write simething similar to

mygrid.columns(2).Visible = False

in your client code.

E 12°55'05.25"
N 56°04'39.16"

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-13 : 06:13:04

yeah, but let's say right now I don't know which ones do i want to hide. e.g. i don't know that there's no data in april.
so i want - either sql to do it for me or my c# code behind the web? which one would be better and how can i do it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 06:58:03

Let C# do it for you.
It is very easy to loop and see all rows in the grid and if there is or is not data in the column.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources