# The Solution

• 获取入职年限的集合，如1999,2000,2001...etc
• 根据唯一值来统计每年的入职数量，当然是得用动态PIVOT
• 然后写进SP里面

DECLARE @hire_date_years TABLE
(
hire_date_year NVARCHAR(50)
);
INSERT  INTO @hire_date_years
( hire_date_year
)
SELECT DISTINCT
DATEPART(YEAR, Employee.HireDate)
FROM    HumanResources.Employee;

DECLARE @sql_yearlist NVARCHAR(MAX);

SELECT  @sql_yearlist = ISNULL(@sql_yearlist, '') + ','
+ QUOTENAME(hire_date_year)
FROM    @hire_date_years

SET @sql_yearlist = STUFF(@sql_yearlist, 1, 1, '')

SELECT  @sql_yearlist
View Code

DECLARE @hire_date_years TABLE
(
hire_date_year NVARCHAR(50)
);
DECLARE @sql_yearlist NVARCHAR(MAX);
DECLARE @sql_command NVARCHAR(MAX);

INSERT  INTO @hire_date_years
( hire_date_year
)
SELECT DISTINCT
DATEPART(YEAR, Employee.HireDate)
FROM    HumanResources.Employee;

SELECT  @sql_yearlist = ISNULL(@sql_yearlist, '') + N','
+ QUOTENAME(hire_date_year)
FROM    @hire_date_years

SET @sql_yearlist = STUFF(@sql_yearlist, 1, 1, '')

SET @sql_command = N'
WITH    employee_data
AS ( SELECT   Employee.BusinessEntityID ,
Employee.JobTitle ,
DATEPART(YEAR, Employee.HireDate) AS HireDate_Year
FROM     HumanResources.Employee
)

SELECT
JobTitle,' + @sql_yearlist + N'
FROM    employee_data
PIVOT   (COUNT(BusinessEntityID) FOR HireDate_Year IN ('  + @sql_yearlist + N')) pivot_data'

PRINT @sql_command
View Code

IF OBJECT_ID(N'dbo.job_title_year_summary', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.job_title_year_summary;
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE job_title_year_summary
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @hire_date_years TABLE
(
hire_date_year NVARCHAR(50)
);
DECLARE @sql_yearlist NVARCHAR(MAX);
DECLARE @sql_command NVARCHAR(MAX);

INSERT  INTO @hire_date_years
( hire_date_year
)
SELECT DISTINCT
DATEPART(YEAR, Employee.HireDate)
FROM    HumanResources.Employee;

SELECT  @sql_yearlist = ISNULL(@sql_yearlist, '') + N','
+ QUOTENAME(hire_date_year)
FROM    @hire_date_years

SET @sql_yearlist = STUFF(@sql_yearlist, 1, 1, '')

SET @sql_command = N'
WITH    employee_data
AS ( SELECT   Employee.BusinessEntityID ,
Employee.JobTitle ,
DATEPART(YEAR, Employee.HireDate) AS HireDate_Year
FROM     HumanResources.Employee
)

SELECT
JobTitle,' + @sql_yearlist + N'
FROM    employee_data
PIVOT   (COUNT(BusinessEntityID) FOR HireDate_Year IN ('  + @sql_yearlist + N')) pivot_data'

PRINT @sql_command;
EXEC sp_executesql @sql_command;
END
GO
View Code

posted @ 2016-06-07 17:43  Jeffrey Chan  阅读(160)  评论(0编辑  收藏  举报