数据表行列转换

有一表A
id       date              name
1       2007-01-01          CCC
1       2007-01-01          DDD
1       2007-01-01          VV
1       2007-02-01          TTTTT
2       2007-01-01          GG
3       2007-01-02          CCC
3       2007-01-01          CCC
4       2007-01-04          YY

输入1月要得到
日期  CCC     DDD    VV   GG   YY
1号    2       1      1   1    0
2号    1       0      0   0    0
3号    0       0      0   0    0
4号    0       0      0   0    1
.      0       0      0   0    0
.      0       0      0   0    0
.      0       0      0   0    0
31号   0       0      0   0    0
合计   3       1      1   1    1



CREATE TABLE A(id int, date datetime, name varchar(10))
INSERT A SELECT 1, '2007-01-01', 'CCC'
UNION ALL SELECT 1, '2007-01-01', 'DDD'
UNION ALL SELECT 1, '2007-01-01', 'VV'
UNION ALL SELECT 1, '2007-02-01', 'TTTTT'
UNION ALL SELECT 2, '2007-01-01', 'GG'
UNION ALL SELECT 3, '2007-01-02', 'CCC'
UNION ALL SELECT 3, '2007-01-01', 'CCC'
UNION ALL SELECT 4, '2007-01-04', 'YY'
GO

-- 查询的存储过程
CREATE PROC p_qry
@yearmonth int
AS
SET NOCOUNT ON
DECLARE @BeginDate datetime, @EndDate datetime, @row int
SELECT
@BeginDate = CONVERT(datetime, RIGHT(@yearmonth * 100 + 1, 8)),
@EndDate = DATEADD(Month, 1, @BeginDate),
@row = Day(@EndDate - 1)

DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

SELECT
日期 = CASE GROUPING(D.id)
WHEN 0 THEN CONVERT(varchar(10), D.id) + '号'
ELSE '合计' END,
CCC = ISNULL(SUM(CASE A.name WHEN 'CCC' THEN 1 ELSE 0 END), 0),
DDD = ISNULL(SUM(CASE A.name WHEN 'DDD' THEN 1 ELSE 0 END), 0),
VV = ISNULL(SUM(CASE A.name WHEN 'VV' THEN 1 ELSE 0 END), 0),
GG = ISNULL(SUM(CASE A.name WHEN 'GG' THEN 1 ELSE 0 END), 0),
YY = ISNULL(SUM(CASE A.name WHEN 'YY' THEN 1 ELSE 0 END), 0)
FROM(
SELECT * FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
RIGHT JOIN @dt D
ON A.date = D.date
GROUP BY D.ID WITH ROLLUP
ORDER BY GROUPING(D.id), D.id
GO

-- 调用
EXEC p_qry 200601
GO

-- 删除测试
DROP TABLE A
DROP PROC P_qry



-- 调用
EXEC p_qry 200701

-- 结果:

日期CCCDDDVVGGYY
1号00000
2号10000
3号00000
4号00001
5号00000
6号00000
7号00000
8号00000
9号00000
10号00000
11号00000
12号00000
13号00000
14号00000
15号00000
16号00000
17号00000
18号00000
19号00000
20号00000
21号00000
22号00000
23号00000
24号00000
25号00000
26号00000
27号00000
28号00000
29号00000
30号00000
31号00000
合计10001



如果要根据满足条件的name的值来动态确定列名, 则存储过程改为:

-- 查询的存储过程
CREATE PROC p_qry
@yearmonth int
AS
SET NOCOUNT ON
DECLARE @BeginDate datetime, @EndDate datetime, @row int
SELECT
@BeginDate = CONVERT(datetime, RIGHT(@yearmonth * 100 + 1, 8)),
@EndDate = DATEADD(Month, 1, @BeginDate),
@row = Day(@EndDate - 1)

DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

DECLARE @s nvarchar(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(name)
+ '= ISNULL(SUM(CASE A.name WHEN ' + QUOTENAME(name, '''') + ' THEN 1 ELSE 0 END), 0)'
FROM(
SELECT DISTINCT name  FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
IF @@ROWCOUNT = 0
RETURN
SET @s = '
DECLARE @dt TABLE(id int identity(1, 1), date datetime)
SET ROWCOUNT @row
INSERT @dt(date) SELECT NULL FROM syscolumns A, syscolumns B
SET ROWCOUNT 0
UPDATE @dt SET date = DATEADD(Day, id - 1, @BeginDate)

SELECT
日期 = CASE GROUPING(D.id)
WHEN 0 THEN CONVERT(varchar(10), D.id) + ''号''
ELSE ''合计'' END' + @s + '
FROM(
SELECT * FROM A
WHERE date > @BeginDate
AND date < @EndDate
)A
RIGHT JOIN @dt D
ON A.date = D.date
GROUP BY D.ID WITH ROLLUP
ORDER BY GROUPING(D.id), D.id
'
EXEC sp_executesql @s, N'@row int, @BeginDate datetime, @EndDate datetime', @row, @BeginDate, @EndDate
GO