sql - 旋转和汇总数据

Question

我有以下内容:

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

Answer 1

假设不会出现间隙，使用:

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

...会给你:

pages
-------
1-4 = 100 Copies
5-5 = 50 Copies
6-6 = 25 Copies