旋转和汇总数据
旋转和汇总数据
提问于 2010-06-26 04:59:06
我有以下几点：
declare @PrintJob TABLE (
PageNumber Int,
Copies Int
)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(1,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(2,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(3,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(4,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(5,50)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(6,25)
SELECT * FROM @PrintJob问:有没有办法在Microsoft SQL Server 2005中生成以下输出？
Pages 1-4 = 100 Copies, 5-5 = 50 Copies, 6-6 = 25 Copies回答 3
Stack Overflow用户
回答已采纳
发布于 2010-06-26 05:09:16
假设不会出现间隙，请使用：
SELECT CAST(MIN(pj.pagenumber) AS VARCHAR(max)) +'-'+ CAST(MAX(pj.pagenumber) AS VARCHAR(max)) +' = '+ CAST(pj.copies AS VARCHAR(max)) +' Copies' AS pages
FROM PRINTJOB pj
GROUP BY pj.copies
ORDER BY pj.copies DESC...will为您提供：
pages
-------
1-4 = 100 Copies
5-5 = 50 Copies
6-6 = 25 CopiesStack Overflow用户
发布于 2010-06-26 15:31:37
top解决方案有一个小问题。如果将以下代码添加到示例代码中：
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(7,100) 你会得到这样的结果：
pages
------
1-7 = 100 Copies
5-5 = 50 Copies
6-6 = 25 Copies困难的部分是识别不同的基团，这些基团是由序列中拷贝数值的变化确定的。
我的建议如下。它由T-SQL挑战赛获胜者Neeraj Mathur的代码修改而来。链接如下：
http://beyondrelational.com/blogs/tc/archive/2009/11/27/tsql-challenge-13-solution-by-neeraj-mathur-and-other-tsql-heros.aspx
代码：
declare @PrintJob TABLE (
PageNumber Int,
Copies Int
)
/* Load the table */
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(1,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(2,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(3,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(4,100)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(5,50)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(6,25)
INSERT INTO @PrintJob(PageNumber,Copies) VALUES(7,100)
/* Set up the string for the final result */
DECLARE @str VARCHAR(MAX)
SET @str = 'Pages '
/* Build a cte with all rows plus a row number for each row */
;WITH cte1 AS (
SELECT
PageNumber,
Copies,
ROW_NUMBER() OVER (ORDER BY PageNumber) AS RowNumber
FROM @PrintJob
),
/*
Build a second, recursive cte that increments a
group number each time the Copies value changes
*/
cte2 AS (
SELECT
PageNumber,
Copies,
RowNumber,
1 AS GroupID
FROM cte1
WHERE RowNumber = 1
UNION ALL
SELECT
c1.PageNumber,
c1.Copies,
c1.RowNumber,
CASE WHEN c1.Copies <> c2.Copies THEN GroupID + 1 ELSE GroupID END AS GroupID
FROM cte2 c2
INNER JOIN cte1 c1
ON c1.RowNumber = c2.RowNumber + 1
)
/*
Get the min and max values for each Group
of pages that repeats the Copies value
and assign that to a string
*/
SELECT
@str = @str
+ CONVERT(VARCHAR(100), StartPage) + '-'
+ CONVERT(VARCHAR(100), EndPage) + ' = ' +
+ CONVERT(VARCHAR(100), Copies) + ' Copies, '
FROM (
SELECT
GroupID,
MIN(PageNumber) AS StartPage,
MAX(PageNumber) AS EndPage,
Copies
FROM cte2
GROUP BY
GroupID,
Copies
) t1
ORDER BY GroupID
/* Get the string but cut off the last comma */
SELECT LEFT(@str, LEN(@str)-1)结果：
------
Pages 1-4 = 100 Copies, 5-5 = 50 Copies, 6-6 = 25 Copies, 7-7 = 100 CopiesStack Overflow用户
发布于 2010-06-26 05:06:44
select '1-4 = '+
cast(SUM(case when PageNumber between 1 and 4 then Copies else 0 end) as varchar(10))+
' Copies , '+
'5-5 = '+
cast(SUM(case when PageNumber =5 then Copies else 0 end) as varchar(10))+' Copies , '+
'6-6 = '+
cast(SUM(case when PageNumber =6 then Copies else 0 end) as varchar(10))+' Copies'
from @PrintJob页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3121407
复制相关文章
相似问题
腾讯云开发者
