存储过程实现列转行报表

例如：求人员年龄结构的报表

CREATE TABLE TAB1(ID BIGINT IDENTITY(1,1),Beg Int,En Int,P varchar(100))
CREATE TABLE TAB2(ID BIGINT IDENTITY(1,1),Name varchar(100),Job_Date Date)

GO

INSERT INTO TAB1
SELECT 0,3,'一级员工'
Union ALL
SELECT 4,4,'二级员工'
Union ALL
SELECT 5,5,'三级员工'
Union ALL
SELECT 6,7,'四级员工'
Union ALL
SELECT 8,100,'五级员工'

GO
INSERT INTO TAB2
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2011-01-01'
Union All
SELECT '一','2001-01-01'
Union All
SELECT '一','2005-01-01'
Union All
SELECT '一','2008-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
Union All
SELECT '一','2008-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
Union All
SELECT '一','2009-01-01'
Union All
SELECT '一','2010-01-01'
GO

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'Select ';
SELECT @SQL = @SQL + ' (Select Count(1) From TAB2 Where  DATEDIFF(YEAR,Job_Date,GETDATE()) Between ' 
	+ Cast(Beg AS VARCHAR) + ' And ' + CAST(En AS VARCHAR)+ ' ) [' + P + '],'
From TAB1

SELECT @SQL = LEFT(@SQL,LEN(@SQL)-1)

EXEC(@SQL)

DROP TABLE TAB1
DROP TABLE TAB2