123123, Sql Server

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 @Columns as VARCHAR(MAX)

SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(sd.item)
from (select DISTINCT item from #SampleData) sd

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 
'SELECT name, location, ' + @Columns + '
FROM
(
    SELECT  CASE WHEN sd.Location is null then ''Total '' + sd.Name
			 ELSE sd.Name
		  END as Name, 
		  sd.Name as GroupName,
		  sd.Location, sd.item, count(item) AS CountValue
    FROM #SampleData sd
    GROUP BY GROUPING SETS ((sd.Name, sd.Location, sd.item),(sd.Name, sd.item))

) as PivotData
PIVOT
(
    sum(CountValue)
    for item  IN (' + @Columns + ')
) AS PivotResult
Order by GroupName, name
'

EXEC(@SQL)

DROP TABLE #SampleData

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