You can't remove the aggregate function, but you can construct your query according to your needs. You can do this at least in two ways:
1. Static PIVOT query.
For example, if the column "ITEM_HEAD_ID" values can only be like 1, 2 and 3, then this is what you need. According to your given data, you can use the following query:
SELECT *, (ISNULL([1], 0 ) + ISNULL([2], 0) + ISNULL([3], 0)) AS [Total]
FROM (SELECT [TRXID],
[ITEM_HEAD_ID],
[ITEM_HEAD_AMT]
FROM [Table]) AS t
PIVOT (MAX([ITEM_HEAD_AMT])
FOR [ITEM_HEAD_ID] IN ([1], [2], [3])) AS p;
Note:
[Table] is the name of your table.
The result:
TRXID 1 2 3 Total
6 100.00 100.00 300.00 500.00
7 100.00 100.00 300.00 500.00
2. Dynamic PIVOT query.
If the column "ITEM_HEAD_ID" values are not static, then this is what you need. Let's say that you have the following data:
TRXID ITEM_HEAD_ID ITEM_HEAD_AMT
6 1 100.00
6 2 100.00
6 3 300.00
7 1 100.00
7 2 100.00
7 3 300.00
7 4 1.00
Example of dynamic query:
DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));
INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(VARCHAR(48), [ITEM_HEAD_ID]) + ']'
FROM [Table];
DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);
SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR(50), [ColumnName])
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, ''));
SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(VARCHAR(50), [ColumnName]) + ', 0)'
FROM @ColumnsTable
FOR XML PATH('')), 1, 3, ''));
SET @SQL = 'SELECT *, (' + @TotalColumn + ') AS [Total]
FROM (SELECT [TRXID],
[ITEM_HEAD_ID],
[ITEM_HEAD_AMT]
FROM [Table]) AS t
PIVOT (MAX([ITEM_HEAD_AMT])
FOR [ITEM_HEAD_ID] IN (' + @PivotColumns + ')) AS p;';
EXEC(@SQL);
The result:
TRXID 1 2 3 4 Total
6 100.00 100.00 300.00 NULL 500.00
7 100.00 100.00 300.00 1.00 501.00
Please let me know how it works for you.