0

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?

Shuvayan Das
  • 1,198
  • 3
  • 20
  • 40
  • What is your database? Different databases have different sintaxes. Also this is one of the most answered questions around here in the sql subjects. Search for pivoting table or transpose rows to columns – Jorge Campos Dec 20 '17 at 14:26
  • This is redshift @JorgeCampos. Ya I know this is a common query but I could not find anything specific to my requirement. – Shuvayan Das Dec 20 '17 at 14:32
  • You don't need to have the exact same data or requirements. It is about the structure of the query. It will be the same – Jorge Campos Dec 20 '17 at 14:34
  • S, BB, and V are the only possible services? Usually pivot requires you to be explicit on the columns you're pivoting. – justiceorjustus Dec 20 '17 at 14:36
  • Hello @justiceorjustus, There can be around 200 services , for the ones the user has not used the entry will be zero. – Shuvayan Das Dec 20 '17 at 14:38
  • @ShuvayanDas Eeesh. You really want to end up with 200 columns in your final result? Check out "Complex PIVOT Example" here: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx You must explicitly select each possible column, therefore you're going to have to do it around 200 times. You can use a tool like Excel to make the query for you since it's just repetition... give it a shot with the link... using `DATEDIFF()` instead of `COUNT()` in the pivot. If not, post what you tried and you need to list every possible `distinct` service. – justiceorjustus Dec 20 '17 at 14:42
  • @ShuvayanDas Otherwise, if you don't want to do the columns explicitly, you'd have to use dynamic SQL. Good luck. – justiceorjustus Dec 20 '17 at 14:45

1 Answers1

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.

justiceorjustus
  • 2,017
  • 1
  • 19
  • 42