Query Help
-
Hi, I have the following query
SELECT iAccountID,iViolatorID,Count(iViolatorID) FROM tbTempQNotificationRequest (NOLOCK)
WHERE IsProcessed = 0 AND sdtStatusDate >= @sdtCurrentDate AND sdtStatusDate < @sdtCurrentDate + 1
GROUP BY iAccountID,vcGroupByValue,iViolatorIDIs it possible say when I group by the result set return iAccount|iViolatorID|Count 1-------|1----------|3 2-------|2----------|5 I would like to have a fourth column say AccountNo and I want that it should contain comma seperated values of AccountNo against the group by columns.e.g. iAccount|iViolatorID|Count|AcccountNo 1-------|1----------|3----|X12,X23,X34 2-------|2----------|5----|X45,X56,X65,X32,X98 I hope my question is clear and understandable
-
Hi, I have the following query
SELECT iAccountID,iViolatorID,Count(iViolatorID) FROM tbTempQNotificationRequest (NOLOCK)
WHERE IsProcessed = 0 AND sdtStatusDate >= @sdtCurrentDate AND sdtStatusDate < @sdtCurrentDate + 1
GROUP BY iAccountID,vcGroupByValue,iViolatorIDIs it possible say when I group by the result set return iAccount|iViolatorID|Count 1-------|1----------|3 2-------|2----------|5 I would like to have a fourth column say AccountNo and I want that it should contain comma seperated values of AccountNo against the group by columns.e.g. iAccount|iViolatorID|Count|AcccountNo 1-------|1----------|3----|X12,X23,X34 2-------|2----------|5----|X45,X56,X65,X32,X98 I hope my question is clear and understandable
One way to do this is to create a function where you pass in an id that returns a comma seperated string of account numbers for that ID. Then your query would look something like SELECT iAccountID, iViolatorID, Count(iViolatorID), GetAccountNumbers(iViolatorID) AS AccountNos FROM tbTempQNotificationRequest This will slow down the query a bit but it'll work. Hope this helps.
-
One way to do this is to create a function where you pass in an id that returns a comma seperated string of account numbers for that ID. Then your query would look something like SELECT iAccountID, iViolatorID, Count(iViolatorID), GetAccountNumbers(iViolatorID) AS AccountNos FROM tbTempQNotificationRequest This will slow down the query a bit but it'll work. Hope this helps.
Hi, Thanks for the answer. You know i could call a function but the thing is this query is a part of function being called from stored procedure. My scenario is complex and calling another function will effect the procedure performance further as currently procedure takes 5 seconds to execute. So I was looking for something if we could accomplish it through SQL. Though thanks anyways. Regards,