SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
	Mar  4 2020 00:59:26 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table #taghits (userid int, email varchar(20), tagname varchar(20), hits int, lastview date)

insert into #taghits select 1, 'email1@here.com', 'tag1', 3, '2020-03-24';
insert into #taghits select 2, 'email2@here.com', 'tag1', 1, '2020-03-17';
insert into #taghits select 2, 'email2@here.com', 'tag2', 1, '2020-03-18';
insert into #taghits select 3, 'email3@here.com', 'tag1', 2, '2020-03-25';
insert into #taghits select 3, 'email3@here.com', 'tag2', 5, '2020-03-28';

select * from #taghits;


userid
email
tagname
hits
lastview


1
email1@here.com
tag1
3
2020-03-24

2
email2@here.com
tag1
1
2020-03-17

2
email2@here.com
tag2
1
2020-03-18

3
email3@here.com
tag1
2
2020-03-25

3
email3@here.com
tag2
5
2020-03-28


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((select distinct ', 
            SUM(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [hits] ELSE 0 END) AS [' + CAST(tagname as varchar(10)) + '_hits],
            MAX(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [lastview] ELSE NULL END) AS [' + CAST(tagname as varchar(10)) + '_lastview]'
            /*---------------You can add other columns here similar to above--------------*/
            FROM #taghits 
            FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @query = 'SELECT userid, email, ' + @Cols + '  FROM #taghits group by userid, email' 

--print (@query)
exec(@query)


userid
email
tag1_hits
tag1_lastview
tag2_hits
tag2_lastview


1
email1@here.com
3
2020-03-24
0
null

2
email2@here.com
1
2020-03-17
1
2020-03-18

3
email3@here.com
2
2020-03-25
5
2020-03-28


Warning: Null value is eliminated by an aggregate or other SET operation.

userid	email	tagname	hits	lastview
1	email1@here.com	tag1	3	2020-03-24
2	email2@here.com	tag1	1	2020-03-17
2	email2@here.com	tag2	1	2020-03-18
3	email3@here.com	tag1	2	2020-03-25
3	email3@here.com	tag2	5	2020-03-28

userid	email	tag1_hits	tag1_lastview	tag2_hits	tag2_lastview
1	email1@here.com	3	2020-03-24	0	null
2	email2@here.com	1	2020-03-17	1	2020-03-18
3	email3@here.com	2	2020-03-25	5	2020-03-28