SQL Server 2005 pivot query help
I'm trying out the pivot function in SQL Server 2005 for the first time and running into a wall getting the result set I desire.
I have two tables defects
and employees
. I'm trying to get all the defects for employees in my employee
table and I don't care about any defects assigned to people outside those that are entered in my employee table (I am thinking to join to it, I have not tried a sub query yet but don't think I'll need to)
I want to get results grouped by (pivoted on) defects.severity
field. The fields I want in the result set are: severity, assigne开发者_高级运维d to, employee.name, fix data
Basically I want to roll up the data according to severity and show a count based on the severity levels of defects
Here are my tables:
CREATE TABLE [dbo].[Employees]
(
[EmployeeId] [int] NOT NULL,
[ManagerId] [int] NULL,
[NTID] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FullName] [nvarchar](100) NOT NULL,
[ReportingGroup] [nchar](10) NOT NULL,
[Added] [datetime] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[UpdateBy] [nvarchar](50) NOT NULL
)
CREATE TABLE [dbo].[defects]
(
[Defect ID] [float] NULL,
[SubSystem] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL,
[Severity] [nvarchar](255) NULL,
[FixDate] [nvarchar](255) NULL,
[Assigned To] [nvarchar](255) NULL,
[Summary] [nvarchar](255) NULL,
[Product List] [nvarchar](255) NULL,
[Development Type] [nvarchar](255) NULL,
[Defect Category] [nvarchar](255) NULL,
[Defect Cause] [nvarchar](255) NULL,
[Est Ready for Retest] [nvarchar](255) NULL,
[Fix Stage] [nvarchar](255) NULL,
[Planned Fix in Deployment Event] [nvarchar](255) NULL,
[Planned Fix in Event Mgmt Group] [nvarchar](255) NULL,
[Est Fixed Date] [nvarchar](255) NULL,
[Other Owner] [nvarchar](255) NULL
)
i got it with this SQL:
select [Assigned To], [1-Urgent],[2-High],[3-Medium],[4-Low]
from (select [assigned to],[Business Severity] from defects where [assigned to] is not null) D
pivot
(count([Business Severity]) for [Business Severity] in ([1-Urgent],[2-High],[3-Medium],[4-Low])) V
thanks all
精彩评论