DECLARE @tab TABLE ( type VARCHAR(10), dataday DATETIME, point INT ) INSERT INTO @tab SELECT '组别一', '2012-03-02',23 UNION ALL SELECT '组别二', '2012-03-02',23 UNION ALL SELECT '组别三', '2012-03-02',23 UNION ALL SELECT '组别一', '2012-03-04',23 UNION ALL SELECT '组别二', '2012-03-05',23 UNION ALL SELECT '组别二', '2012-03-06',23 UNION ALL SELECT '组别三', '2012-03-07',23 UNION ALL SELECT '组别三', '2012-03-08',23 UNION ALL SELECT '组别二', '2012-03-09',23 UNION ALL SELECT '组别一', '2012-03-10',23 UNION ALL SELECT '组别一', '2012-03-11',23 UNION ALL SELECT '组别三', '2012-03-12',23 UNION ALL SELECT '组别一', '2012-03-13',23 UNION ALL SELECT '组别三', '2012-03-14',23 UNION ALL SELECT '组别一', '2012-03-15',23 UNION ALL SELECT '组别二', '2012-03-16',23 UNION ALL SELECT '组别三', '2012-03-16',23 SELECT * FROM @tab SELECT --那我也显示5条 type, [dataday1]=max(case when [dataday]='2012-03-02' then [point] else 0 end), [dataday2]=max(case when [dataday]='2012-03-03' then [point] else 0 end), [dataday3]=max(case when [dataday]='2012-03-04' then [point] else 0 end), [dataday4]=max(case when [dataday]='2012-03-05' then [point] else 0 end), [dataday5]=max(case when [dataday]='2012-03-06' then [point] else 0 end) FROM @tab GROUP BY type /*type dataday1 dataday2 dataday3 dataday4 dataday5 ---------- ----------- ----------- ----------- ----------- ----------- 组别二 23 0 0 23 23 组别三 23 0 0 0 0 组别一 23 0 23 0 0(所影响的行数为 3 行) */
--借用14楼的数据 CREATE TABLE # ( type VARCHAR(10), dataday DATETIME, point INT ) INSERT INTO # SELECT '组别一', '2012-03-02',23 UNION ALL SELECT '组别二', '2012-03-02',23 UNION ALL SELECT '组别三', '2012-03-02',23 UNION ALL SELECT '组别一', '2012-03-04',23 UNION ALL SELECT '组别二', '2012-03-05',23 UNION ALL SELECT '组别二', '2012-03-06',23 UNION ALL SELECT '组别三', '2012-03-07',23 UNION ALL SELECT '组别三', '2012-03-08',23 UNION ALL SELECT '组别二', '2012-03-09',23 UNION ALL SELECT '组别一', '2012-03-10',23 UNION ALL SELECT '组别一', '2012-03-11',23 UNION ALL SELECT '组别三', '2012-03-12',23 UNION ALL SELECT '组别一', '2012-03-13',23 UNION ALL SELECT '组别三', '2012-03-14',23 UNION ALL SELECT '组别一', '2012-03-15',23 UNION ALL SELECT '组别二', '2012-03-16',23 UNION ALL SELECT '组别三', '2012-03-16',23 DECLARE @CaseSql nvarchar(4000) DECLARE @ExecSql nvarchar(4000) SET @ExecSql='SELECT type,' SELECT @CaseSql=ISNULL(@CaseSql,'')+'MAX(CASE dataday WHEN '''+CONVERT(varchar(20),dataday,120)+''' THEN point ELSE 0 END) AS ['+CONVERT(varchar(20),dataday,120)+'],' FROM # GROUP BY dataday SET @ExecSql=@ExecSql+LEFT(@CaseSql,LEN(@CaseSql)-1)+' FROM # GROUP BY type' EXEC(@ExecSql) /* type 2012-03-02 00:00:00 2012-03-04 00:00:00 2012-03-05 00:00:00 2012-03-06 00:00:00 2012-03-07 00:00:00 2012-03-08 00:00:00 2012-03-09 00:00:00 2012-03-10 00:00:00 2012-03-11 00:00:00 2012-03-12 00:00:00 2012-03-13 00:00:00 2012-03-14 00:00:00 2012-03-15 00:00:00 2012-03-16 00:00:00 组别二 23 0 23 23 0 0 23 0 0 0 0 0 0 23 组别三 23 0 0 0 23 23 0 0 0 23 0 23 0 23 组别一 23 23 0 0 0 0 0 23 23 0 23 0 23 0 */
type dataday point
组别一 2012-03-02 23
组别二 2012-03-02 23
组别三 2012-03-02 23
组别一 2012-03-04 23
组别二 2012-03-05 23
组别二 2012-03-06 23
组别三 2012-03-07 23
组别三 2012-03-08 23
组别二 2012-03-09 23
组别一 2012-03-10 23
组别一 2012-03-11 23
组别三 2012-03-12 23
组别一 2012-03-13 23
组别三 2012-03-14 23
组别一 2012-03-15 23
组别二 2012-03-16 23
组别三 2012-03-17 23
2012-03-02 2012-03-03 2012-03-04 2012-03-05 2012-03-06
组别一 23 23 23 23 23
组别二 23 23 23 23 23
组别三 23 23 23 23 23
(
type VARCHAR(10),
dataday DATETIME,
point INT
)
INSERT INTO @tab
SELECT '组别一', '2012-03-02',23 UNION ALL
SELECT '组别二', '2012-03-02',23 UNION ALL
SELECT '组别三', '2012-03-02',23 UNION ALL
SELECT '组别一', '2012-03-04',23 UNION ALL
SELECT '组别二', '2012-03-05',23 UNION ALL
SELECT '组别二', '2012-03-06',23 UNION ALL
SELECT '组别三', '2012-03-07',23 UNION ALL
SELECT '组别三', '2012-03-08',23 UNION ALL
SELECT '组别二', '2012-03-09',23 UNION ALL
SELECT '组别一', '2012-03-10',23 UNION ALL
SELECT '组别一', '2012-03-11',23 UNION ALL
SELECT '组别三', '2012-03-12',23 UNION ALL
SELECT '组别一', '2012-03-13',23 UNION ALL
SELECT '组别三', '2012-03-14',23 UNION ALL
SELECT '组别一', '2012-03-15',23 UNION ALL
SELECT '组别二', '2012-03-16',23 UNION ALL
SELECT '组别三', '2012-03-16',23
SELECT * FROM @tab
SELECT --那我也显示5条
type,
[dataday1]=max(case when [dataday]='2012-03-02' then [point] else 0 end),
[dataday2]=max(case when [dataday]='2012-03-03' then [point] else 0 end),
[dataday3]=max(case when [dataday]='2012-03-04' then [point] else 0 end),
[dataday4]=max(case when [dataday]='2012-03-05' then [point] else 0 end),
[dataday5]=max(case when [dataday]='2012-03-06' then [point] else 0 end)
FROM
@tab
GROUP BY type
/*type dataday1 dataday2 dataday3 dataday4 dataday5
---------- ----------- ----------- ----------- ----------- -----------
组别二 23 0 0 23 23
组别三 23 0 0 0 0
组别一 23 0 23 0 0(所影响的行数为 3 行)
*/
CREATE TABLE #
(
type VARCHAR(10),
dataday DATETIME,
point INT
)
INSERT INTO #
SELECT '组别一', '2012-03-02',23 UNION ALL
SELECT '组别二', '2012-03-02',23 UNION ALL
SELECT '组别三', '2012-03-02',23 UNION ALL
SELECT '组别一', '2012-03-04',23 UNION ALL
SELECT '组别二', '2012-03-05',23 UNION ALL
SELECT '组别二', '2012-03-06',23 UNION ALL
SELECT '组别三', '2012-03-07',23 UNION ALL
SELECT '组别三', '2012-03-08',23 UNION ALL
SELECT '组别二', '2012-03-09',23 UNION ALL
SELECT '组别一', '2012-03-10',23 UNION ALL
SELECT '组别一', '2012-03-11',23 UNION ALL
SELECT '组别三', '2012-03-12',23 UNION ALL
SELECT '组别一', '2012-03-13',23 UNION ALL
SELECT '组别三', '2012-03-14',23 UNION ALL
SELECT '组别一', '2012-03-15',23 UNION ALL
SELECT '组别二', '2012-03-16',23 UNION ALL
SELECT '组别三', '2012-03-16',23
DECLARE @CaseSql nvarchar(4000)
DECLARE @ExecSql nvarchar(4000)
SET @ExecSql='SELECT type,'
SELECT @CaseSql=ISNULL(@CaseSql,'')+'MAX(CASE dataday WHEN '''+CONVERT(varchar(20),dataday,120)+''' THEN point ELSE 0 END) AS ['+CONVERT(varchar(20),dataday,120)+'],' FROM # GROUP BY dataday
SET @ExecSql=@ExecSql+LEFT(@CaseSql,LEN(@CaseSql)-1)+' FROM # GROUP BY type'
EXEC(@ExecSql)
/*
type 2012-03-02 00:00:00 2012-03-04 00:00:00 2012-03-05 00:00:00 2012-03-06 00:00:00 2012-03-07 00:00:00 2012-03-08 00:00:00 2012-03-09 00:00:00 2012-03-10 00:00:00 2012-03-11 00:00:00 2012-03-12 00:00:00 2012-03-13 00:00:00 2012-03-14 00:00:00 2012-03-15 00:00:00 2012-03-16 00:00:00
组别二 23 0 23 23 0 0 23 0 0 0 0 0 0 23
组别三 23 0 0 0 23 23 0 0 0 23 0 23 0 23
组别一 23 23 0 0 0 0 0 23 23 0 23 0 23 0
*/