Demo, Sql Server - rextester

Language: Layout:

CREATE TABLE #SampleData
(
    Name varchar(10),
    Location varchar(20),
    Item varchar(10)
)

INSERT INTO #SampleData
VALUES
('Ron', 'Loc A', 'Pencil'),
('Ron', 'Loc A', 'Pencil'),
('Ron', 'Loc B', 'Pen'),
('Ron', 'Loc B', 'Laptop'),
('Tom', 'Loc A', 'Pencil'),
('Tom', 'Loc B', 'Pencil'),
('Tom', 'Loc B', 'Pen'),
('Tom', 'Loc A', 'Pencil'),
('Tom', 'Loc A', 'Laptop'),
('Tom', 'Loc A', 'Pencil')

DECLARE @Pivot_Columns  AS VARCHAR(MAX),
			@select_Columns VARCHAR(max)

SELECT @Pivot_Columns = Stuff((SELECT DISTINCT ',' + Quotename(item) FROM #SampleData FOR xml path('')), 1, 1, '')
	SELECT @select_Columns = Stuff((SELECT DISTINCT ',Sum(' + Quotename(item) + ') as '+Quotename(item) FROM #SampleData FOR xml path('')), 1, 1, '')

DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = 'SELECT case when grouping(location) = 1 and grouping(name) = 0 then ''Total''+ '' '' + name 
	when grouping(location) = 1 and grouping(name) = 1 then ''Total'' 
	else name end Name, location, '
			   + @select_Columns + '
	FROM
	(
		SELECT name, location, item
		FROM #SampleData

) as PivotData
	PIVOT
	(
		count(item)
		for item  IN ('
			   + @Pivot_Columns + ')
	) AS PivotResult
	group by name,location with rollup 
	'

EXEC(@SQL)

View schema
Execution time: 0,03 sec, rows selected: 10, rows affected: 10, absolute service time: 0,17 sec