Getting the counts of the number of months a user has been using a particular service in SQL

Question

I have data like below:

user_id month_id    service
895       201612    S
262       201612    V
5300      201612    BB

Now there can be users who have used more than one service in a year, and I would like to have a query which gives me that. For example say 895 has used S for 3 months and BB for 4 months and then his latest service is V. So :

user_id S BB V
895     3 4  5

How do I do this pivot and count in SQL , can someone please help me?

Answer 1

Here is how you would do it dynamically:

DECLARE @Cols AS NVARCHAR(MAX)
    ,@Query AS NVARCHAR(MAX);

SET @Cols = STUFF((
            SELECT DISTINCT ',' + QUOTENAME(service)
            FROM YourTable
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT user_id, ' + @Cols + ' from 
            (
                select user_id
                    , month_id
                    , service
                from YourTable
           ) x
            pivot 
            (
                 COUNT(month_id)
                for service in (' + @Cols + ')
            ) p '

EXECUTE(@Query)

Edit: Didn't realize it was a COUNT of month_id and not DATEDIFF(mm, CONVERT(DATETIME, month_id + ''01'',GETDATE()). Updated.