How to apply group by in sql

Question

Can the same logic be applied in sql stored procedure itself to get the same result as done in the following link post instead of using LINQ?

Linq query having group by

This is my stored procedure

CREATE PROC [dbo].[WF_SPScanningReport_S]    
(    
        @COMPANYID VARCHAR(20),    
        @FROMDATE DATETIME,    
        @TODATE  DATETIME,
        @FromRecord int,         
        @ToRecord int,       
        @SortString varchar(200)   
)    
AS    
BEGIN    
declare @Query nvarchar(Max)     
    if(ISNULL(LTRIM(RTRIM(@SortString)),'') <> '') 
        set @SortString='order by '+@SortString
    else 
        set @SortString='order by IMAGEID'



 SET NOCOUNT ON   
   set @Query=' CREATE TABLE #TABLE     
                (IMAGEID INT ,     
                SCANDATE DATETIME,    
                COUNT INT    
                )    

                INSERT INTO #TABLE(IMAGEID , SCANDATE)    
 SELECT IMAGEID , CAST(CONVERT(VARCHAR(10), SCAN_DATE,101) AS DATETIME) SCANDATE FROM WF_IMAGES     
 WHERE COMPANYID='''+@COMPANYID+''' AND SCAN_DATE BETWEEN '''+cast(@FromDate as varchar)+''' AND '''+cast(@ToDate as varchar)+''' AND IMAGE_NAME <> ''BLANK.PDF'' AND ISNULL(''Reviewed'','''') <> ''X''



  UPDATE #TABLE    
     SET COUNT = S.COUNT1    
     FROM #TABLE T, (SELECT COUNT(SCANDATE) AS COUNT1 ,SCANDATE FROM #TABLE GROUP BY SCANDATE) S    
     WHERE T.SCANDATE= S.SCANDATE    



SELECT * FROM   (SELECT Distinct row_number() over ('+@SortString+') as RowNum,
  IMAGEID, SCANDATE, COUNT FROM #TABLE )AS RowConstrainedResult     
                    Where RowNum >'+cast(@FromRecord as varchar)+' AND RowNum <= '+cast (@ToRecord as varchar)+'
                     DROP TABLE #TABLE '
     exec (@Query)  
            PRINT @Query    

 SET NOCOUNT OFF  
END

This is my SP result

RowNum  IMAGEID         SCANDATE                COUNT
1   10000131    2012-07-04 00:00:00.000 1
2   10002626    2012-08-03 00:00:00.000 1
3   10003348    2012-09-06 00:00:00.000 1
4   10003589    2012-09-15 00:00:00.000 8
5   10003590    2012-09-15 00:00:00.000 8
6   10003591    2012-09-15 00:00:00.000 8
7   10003592    2012-09-15 00:00:00.000 8
8   10003595    2012-09-15 00:00:00.000 8
9   10003596    2012-09-15 00:00:00.000 8
10  10003598    2012-09-15 00:00:00.000 8
11  10003599    2012-09-15 00:00:00.000 8
12  10004808    2012-09-20 00:00:00.000 14
13  10004809    2012-09-20 00:00:00.000 14
14  10004810    2012-09-20 00:00:00.000 14
15  10004811    2012-09-20 00:00:00.000 14
16  10004812    2012-09-20 00:00:00.000 14
17  10004813    2012-09-20 00:00:00.000 14
18  10004814    2012-09-20 00:00:00.000 14
19  10004815    2012-09-20 00:00:00.000 14
20  10004816    2012-09-20 00:00:00.000 14
21  10004817    2012-09-20 00:00:00.000 14
22  10004818    2012-09-20 00:00:00.000 14
23  10004819    2012-09-20 00:00:00.000 14
24  10004820    2012-09-20 00:00:00.000 14
25  10004823    2012-09-20 00:00:00.000 14
26  10006686    2012-09-21 00:00:00.000 3
27  10006687    2012-09-21 00:00:00.000 3
28  10006689    2012-09-21 00:00:00.000 3
29  10006806    2012-09-27 00:00:00.000 1
30  10006816    2012-09-28 00:00:00.000 1
31  10006840    2012-10-02 00:00:00.000 1
32  10006877    2012-10-03 00:00:00.000 1
33  10006923    2012-10-05 00:00:00.000 3
34  10006936    2012-10-05 00:00:00.000 3
35  10006937    2012-10-05 00:00:00.000 3
36  10620408    2013-03-11 00:00:00.000 1
37  10622755    2013-07-01 00:00:00.000 8
38  10622756    2013-07-01 00:00:00.000 8
39  10622761    2013-07-01 00:00:00.000 8
40  10622765    2013-07-01 00:00:00.000 8
41  10622771    2013-07-01 00:00:00.000 8
42  10622772    2013-07-01 00:00:00.000 8
43  10622773    2013-07-01 00:00:00.000 8
44  10622774    2013-07-01 00:00:00.000 8
45  10622891    2013-07-02 00:00:00.000 14
46  10622892    2013-07-02 00:00:00.000 14
47  10622893    2013-07-02 00:00:00.000 14
48  10622895    2013-07-02 00:00:00.000 14
49  10622896    2013-07-02 00:00:00.000 14
50  10622897    2013-07-02 00:00:00.000 14
51  10622898    2013-07-02 00:00:00.000 14
52  10622899    2013-07-02 00:00:00.000 14
53  10622900    2013-07-02 00:00:00.000 14
54  10622901    2013-07-02 00:00:00.000 14
55  10622902    2013-07-02 00:00:00.000 14
56  10622903    2013-07-02 00:00:00.000 14
57  10622904    2013-07-02 00:00:00.000 14
58  10622905    2013-07-02 00:00:00.000 14
59  10622931    2013-07-03 00:00:00.000 10
60  10622932    2013-07-03 00:00:00.000 10
61  10622933    2013-07-03 00:00:00.000 10
62  10622946    2013-07-03 00:00:00.000 10
63  10622950    2013-07-03 00:00:00.000 10
64  10622951    2013-07-03 00:00:00.000 10
65  10622952    2013-07-03 00:00:00.000 10
66  10622953    2013-07-03 00:00:00.000 10
67  10622954    2013-07-03 00:00:00.000 10
68  10622956    2013-07-03 00:00:00.000 10
69  10622979    2013-07-04 00:00:00.000 5
70  10622980    2013-07-04 00:00:00.000 5
71  10622981    2013-07-04 00:00:00.000 5
72  10623017    2013-07-04 00:00:00.000 5
73  10623018    2013-07-04 00:00:00.000 5
74  10623026    2013-07-05 00:00:00.000 9
75  10623027    2013-07-05 00:00:00.000 9
76  10623028    2013-07-05 00:00:00.000 9
77  10623029    2013-07-05 00:00:00.000 9
78  10623030    2013-07-05 00:00:00.000 9
79  10623031    2013-07-05 00:00:00.000 9
80  10623032    2013-07-05 00:00:00.000 9
81  10623033    2013-07-05 00:00:00.000 9
82  10623034    2013-07-05 00:00:00.000 9
83  10623059    2013-07-08 00:00:00.000 7
84  10623060    2013-07-08 00:00:00.000 7
85  10623061    2013-07-08 00:00:00.000 7
86  10623062    2013-07-08 00:00:00.000 7
87  10623063    2013-07-08 00:00:00.000 7
88  10623064    2013-07-08 00:00:00.000 7
89  10623065    2013-07-08 00:00:00.000 7
90  10623107    2013-07-09 00:00:00.000 1
91  10623112    2013-07-10 00:00:00.000 5
92  10623113    2013-07-10 00:00:00.000 5
93  10623122    2013-07-10 00:00:00.000 5
94  10623123    2013-07-10 00:00:00.000 5
95  10623124    2013-07-10 00:00:00.000 5
96  10623142    2013-07-12 00:00:00.000 2
97  10623143    2013-07-12 00:00:00.000 2
98  10623172    2013-07-15 00:00:00.000 17
99  10623173    2013-07-15 00:00:00.000 17
100 10623174    2013-07-15 00:00:00.000 17
101 10623175    2013-07-15 00:00:00.000 17
102 10623176    2013-07-15 00:00:00.000 17
103 10623177    2013-07-15 00:00:00.000 17
104 10623178    2013-07-15 00:00:00.000 17
105 10623179    2013-07-15 00:00:00.000 17
106 10623180    2013-07-15 00:00:00.000 17
107 10623181    2013-07-15 00:00:00.000 17
108 10623182    2013-07-15 00:00:00.000 17
109 10623183    2013-07-15 00:00:00.000 17
110 10623184    2013-07-15 00:00:00.000 17
111 10623185    2013-07-15 00:00:00.000 17
112 10623186    2013-07-15 00:00:00.000 17
113 10623187    2013-07-15 00:00:00.000 17
114 10623188    2013-07-15 00:00:00.000 17
115 10623195    2013-07-16 00:00:00.000 27
116 10623196    2013-07-16 00:00:00.000 27
117 10623199    2013-07-16 00:00:00.000 27
118 10623200    2013-07-16 00:00:00.000 27
119 10623203    2013-07-16 00:00:00.000 27
120 10623204    2013-07-16 00:00:00.000 27
121 10623206    2013-07-16 00:00:00.000 27
122 10623207    2013-07-16 00:00:00.000 27
123 10623212    2013-07-16 00:00:00.000 27
124 10623213    2013-07-16 00:00:00.000 27
125 10623214    2013-07-16 00:00:00.000 27
126 10623215    2013-07-16 00:00:00.000 27
127 10623216    2013-07-16 00:00:00.000 27
128 10623217    2013-07-16 00:00:00.000 27
129 10623218    2013-07-16 00:00:00.000 27
130 10623223    2013-07-16 00:00:00.000 27
131 10623225    2013-07-16 00:00:00.000 27
132 10623227    2013-07-16 00:00:00.000 27
133 10623228    2013-07-16 00:00:00.000 27
134 10623229    2013-07-16 00:00:00.000 27
135 10623230    2013-07-16 00:00:00.000 27
136 10623231    2013-07-16 00:00:00.000 27
137 10623232    2013-07-16 00:00:00.000 27
138 10623234    2013-07-16 00:00:00.000 27
139 10623236    2013-07-16 00:00:00.000 27
140 10623237    2013-07-16 00:00:00.000 27
141 10623238    2013-07-16 00:00:00.000 27
142 10623240    2013-07-17 00:00:00.000 6
143 10623241    2013-07-17 00:00:00.000 6
144 10623242    2013-07-17 00:00:00.000 6
145 10623244    2013-07-17 00:00:00.000 6
146 10623247    2013-07-17 00:00:00.000 6
147 10623248    2013-07-17 00:00:00.000 6
148 10623841    2013-09-11 00:00:00.000 2
149 10623848    2013-09-11 00:00:00.000 2
150 10623921    2013-09-20 00:00:00.000 2
151 10623923    2013-09-20 00:00:00.000 2
152 10624106    2013-10-09 00:00:00.000 3
153 10624108    2013-10-09 00:00:00.000 3
154 10624116    2013-10-09 00:00:00.000 3
155 10624521    2013-11-23 00:00:00.000 2
156 10624523    2013-11-23 00:00:00.000 2
157 10625218    2013-12-31 00:00:00.000 2
158 10625219    2013-12-31 00:00:00.000 2
159 10625220    2014-01-02 00:00:00.000 1
160 10625245    2014-01-07 00:00:00.000 5
161 10625247    2014-01-07 00:00:00.000 5
162 10625250    2014-01-07 00:00:00.000 5
163 10625251    2014-01-07 00:00:00.000 5
164 10625252    2014-01-07 00:00:00.000 5
165 10625266    2014-01-09 00:00:00.000 1

And I need something like this

11/27/2007 12:00:00 AM 1001527,1001528,1001529,1001530,1001531,1001532,1001533,1001534,1001535,1001536,1001537,1001538,1001539,1001540,1001541,1001542 16 
  11/20/2008 12:00:00 AM 1002501,1002502,1002503,1002504,1002505,1002506,1002507,1002508,1002509,1002510,1002511,1002512,1002513,1002514,1002515,1002516,1002517,1002518,1002519,1002520 20 
  7/5/2011 12:00:00 AM 1015237,1015238 2 
  7/6/2011 12:00:00 AM 1015248,1015249,1015259,1015260,1015286,1015287,1015288,1015289,1015290,1015291,1015292,1015293 12

First column has scandate on which Group By is done, Second has image ids having same scan date in single cell - comma seperated, and lastly count colimn with count of image ids in single cell

I have tried by querying datatable using linq, but i need to get the result in the datatable itself rather than applying linq on datatable — user2783853, Feb 03 '14 at 05:28
I have mentioned the result which I expect from the SP, which currently I am achieving with LINQ. But that wont satisfy my code needs, So I need the result directly from SP itself in datatable. — user2783853, Feb 03 '14 at 05:43

score 0 · Accepted Answer · edited May 23 '17 at 12:11

The querstion comma delimited list as a single string, T-SQL provides an illustration of how you can get a comma-delimited list grouped by a particular field.

The following code-snippet shows how you could take your resultant dataset and pivot it as illustrated in question above.

select distinct t1.scantime,
STUFF(
     (SELECT ',' + convert(varchar(10), t2.ImageId, 120)
      FROM yourtable t2
      where t1.scantime = t2.scantime
      FOR XML PATH (''))
      , 1, 1, '')  AS imageids
from yourtable t1

However, you will want to refactor your existing stored proc, so that you are not doing unnecessary intermediate queries to get to this result.

How to apply group by in sql

1 Answers1