开发者

Rotate and summarize data

I have the following:

declare @PrintJob TABLE (
  PageNumber Int,
  Copies Int
)

INSERT INTO @PrintJob(PageNumber,Copies) VALUES(1,100)
INSERT INTO @Print开发者_JAVA技巧Job(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

Q: Is there a way to produce the following output in Microsoft SQL Server 2005?

Pages 1-4 = 100 Copies, 5-5 = 50 Copies, 6-6 = 25 Copies


Assuming gaps can not occur, use:

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 give you:

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


There is a slight problem with the top solution. If you add this to the sample code:

INSERT INTO @PrintJob(PageNumber,Copies) VALUES(7,100) 

You will get this:

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

The hard part is identifying the different groups which are determined by a change in the Copies value in the sequence.

My suggestion is below. It is modified from the code of T-SQL challenge winner Neeraj Mathur. Here is the link:

http://beyondrelational.com/blogs/tc/archive/2009/11/27/tsql-challenge-13-solution-by-neeraj-mathur-and-other-tsql-heros.aspx

Code:

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)

Result:

------
Pages 1-4 = 100 Copies, 5-5 = 50 Copies, 6-6 = 25 Copies, 7-7 = 100 Copies


 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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜