CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
DavidB
DavidB

Reputation: 2596

SQL Grouping and concatenation

I have a log that captures user actions on my site; this is then displayed back to users on the homepage. Actions can take the form of adding a page, liking a page, updating a page etc.

“John Smith has updated the test page”

I have been asked to group these actions, so rather than twenty different entries for updating the same page on the same day, it will show one entry:

“John Smith and twenty others have updated the test page”

Ideally the name displayed will be the last person to perform that action, so if someone else updated the same page, on the same day it would read:

“Steve Jones and twenty-one others have updated the test page”

I do not want to group all types of actions like this, only some. It is further complicated that the different perspectives, (ie John Smith would see “You” instead of “John Smith”) are stored in a separate table.

I’ve been struggling to see how I will achieve this through the SP, and was wondering if this was plausible? I can see exactly how I would do it in .net in code behind, so perhaps I should take this approach?

Your thoughts are very welcome.

Upvotes: 1

Views: 65

Answers (1)

roman
roman

Reputation: 117596

window functions will help you! :) You can do something like this:

with cte as (
    select
        *,
        row_number() over(partition by page_id order by date asc) as rn,
        count(*) over(partition by page_id) as cnt
    from tasks
)
select
    user_name + isnull(' and ' + cast(nullif(cnt - 1, 0) as nvarchar(max)) + ' others', '') + ' ' +
    case
        when action = 'update' then 'updated'
    end + ' this page (id: ' + cast(page_id as nvarchar(max)) + ')'
from cte
where rn = 1

=> sql fiddle demo with some test cases. You can modify it further, change 'others' to 'other' if there's just one other user and so on.

Upvotes: 1

Related Questions