0

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

Improve this question
3
  • What did you try ? You already have similar solution. Commented Feb 3, 2014 at 5:14
  • 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 Commented Feb 3, 2014 at 5: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. Commented Feb 3, 2014 at 5:43

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.