- SQL code
CREATE TABLE #TEST (ID INT, SEGMENT CHAR (1),RESPONSES INT,CHECKED SMALLDATETIME)
有表如上
RESPONSES 代表SEGMENT 出现在 日期 CHECKED 的次数，要求：
写一个query 语句 总结 在每个SEGMENT 和 每个CHECKED 的 RESPONSES
不要硬编码 如果以后加了D 或者 Segments 重命名了 应该有相应改变
样本：
- SQL code
INSERT INTO #TEST VALUES (1, 'A', 0, '2009-05-01')INSERT INTO #TEST VALUES (2, 'B', 1, '2009-05-01')INSERT INTO #TEST VALUES (3, 'C', 0, '2009-05-01')INSERT INTO #TEST VALUES (4, 'A', 0, '2009-05-02')INSERT INTO #TEST VALUES (5, 'B', 2, '2009-05-02')INSERT INTO #TEST VALUES (6, 'C', 1, '2009-05-02')INSERT INTO #TEST VALUES (7, 'A', 1, '2009-05-03')INSERT INTO #TEST VALUES (8, 'B', 0, '2009-05-03')INSERT INTO #TEST VALUES (9, 'C', 2, '2009-05-03')
输出
CHECKED A B C
1 2009-05-01 0 1 0
2 2009-05-02 0 2 1
3 2009-05-03 1 0 2
------解决方案--------------------
- SQL code
DECLARE @s VARCHAR(MAX)SELECT @s=ISNULL(@s+',','')+QUOTENAME(SEGMENT) FROM #TEST GROUP BY SEGMENTEXEC('SELECT * FROM ( SELECT SEGMENT , RESPONSES , CHECKED FROM #TEST ) AS t PIVOT ( MAX(RESPONSES) FOR SEGMENT IN ('+@s+') ) AS piv')/*CHECKED A B C2009-05-01 00:00:00 0 1 02009-05-02 00:00:00 0 2 12009-05-03 00:00:00 1 0 2*/