sqlserver 对多条数据分组

在开发中，经常会遇到要吧一行行数据按照某一行进行分组

1. USE [OA]
2. GO
3. /****** Object: StoredProcedure [dbo].[usp_report_GatherDataMsgRpt] Script Date: 12/02/2014 10:42:22 ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
12. ALTER PROC [dbo].[usp_report_GatherDataMsgRpt]
13. @datamsgid int
14. AS
15. begin
17. DECLARE @t_dataPublish_id INT ,@t_dataPublishColumn_id INT , @execSQL VARCHAR(4000),@temSql1 VARCHAR(4000) ,@temSql2 VARCHAR(4000)
18. ,@columnKey varchar(200)
20. SELECT @t_dataPublish_id=@datamsgid
22. SELECT @execSQL='' ,@temSql1='',@temSql2='',@columnKey =''
24. DECLARE Cur CURSOR
25. FOR
26. SELECT t1.id
27. FROM dbo.t_dataPublishColumn t1
28. WHERE t1.t_dataPublish_id = @t_dataPublish_id
30. SELECT @execSQL = 'SELECT '
32. OPEN Cur
33. FETCH NEXT FROM Cur INTO @t_dataPublishColumn_id
35. WHILE @@fetch_status = 0
36. BEGIN
37. SELECT @columnKey=''
39. SELECT
40. @columnKey =
41. LTRIM(t2.columnName)
42. FROM t_dataPublishColumn t2
43. WHERE t2.id=@t_dataPublishColumn_id
45. SET @temSql1 = @temSql1 + ',MAX(' + LTRIM(@columnKey) + ') as '+ LTRIM(@columnKey)
47. SET @temSql2 = @temSql2 + ',CASE WHEN t2.t_dataPublishColumn_id='
48. + LTRIM(@t_dataPublishColumn_id) + ' then cellValue END '
49. + LTRIM(@columnKey)
51. FETCH NEXT FROM Cur INTO @t_dataPublishColumn_id
52. END
53. CLOSE Cur
54. DEALLOCATE Cur
57. SELECT @execSQL = @execSQL + SUBSTRING(@temSql1,2,LEN(@temSql1)) + ' FROM (
58. SELECT t2.rownum
59. ' + @temSql2
60. + '
61. FROM t_dataPublishColumn t1,t_dataPublishDetail t2 WHERE t1.id=t2.t_dataPublishColumn_id AND t1.t_dataPublish_id='
62. + LTRIM(@t_dataPublish_id) + ') x GROUP BY x.rownum
63. '
65. PRINT @execSQL
66. EXEC(@execSQL)
67. END