want rows values into columns

Question

below is my query :

select (
    case when activity_type=3 then 'share_count_secret' 
         when activity_type=4 then 'download_count_secret' 
         when activity_type=5 then 'copy_count_secret' end
    )as activity_type ,
    count(distinct user_account_id_fk) as counts
from  activities 
where  target_id = 522556 
and activity_type in(3,4,5) 
group by activity_type;

below is the output of above query:

 activity_type         counts
  share_count           2
  download_count        2

but I want output like:

 share_count     download_count
  2                   2

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 — Dale K, Feb 18 '19 at 09:59
**Please dont SPAM Tags** Are you using MySQL or SQLServer thay are not the same thing at all — RiggsFolly, Feb 18 '19 at 10:03

Fahmi · Answer 1 · 2019-02-18T10:07:25.163

0

You can try using conditional aggregation

select 
  count(case when activity_type=3 then user_account_id_fk end) as 'share_count_secret',
  count(case when activity_type=4 then user_account_id_fk end) as 'download_count_secret'
  count(case when activity_type=5 then user_account_id_fk end) as 'copy_count_secret'
  from  activities 
  where  target_id = 522556 and activity_type in(3,4,5)

edited Feb 18 '19 at 10:07

answered Feb 18 '19 at 09:59

Fahmi

36,607
5
19
28

score 0 · Answer 2 · answered Feb 18 '19 at 10:01

If you are using SQL Server ( I saw you have tagged MySQL also). You may try pivot

;WITH CTE
AS
(
    SELECT 
        ( 
            CASE 
               WHEN activity_type = 3 THEN 'share_count_secret' 
               WHEN activity_type = 4 THEN 'download_count_secret' 
               WHEN activity_type = 5 THEN 'copy_count_secret' 
             END 
        ) AS activity_type, 
        COUNT(DISTINCT user_account_id_fk) AS counts 
        FROM   activities 
            WHERE  target_id = 522556 
                AND activity_type IN( 3, 4, 5 ) 
            GROUP  BY activity_type
)
SELECT
    *
    FROM CTE
    PIVOT
    (
        SUM(counts)
        FOR
        activity_type IN
        (
            [share_count_secret],
            [download_count_secret],
            [copy_count_secret]
        )
    )PVT

want rows values into columns

2 Answers2