Displaying Rows as Columns With Total Displayed on Each Row and Column

Solution

You can uncomment the SELECT statement to check what is happening at each step. We are using Dynamic SQL for displaying the data. Feel free to uncomment anytime in the query and see what is happening.

DECLARE @Count INT = 1
DECLARE @MaxCount INT = 0

---TABLE TO STORE THE USERNAMES IN A SEPERATE TABLE VARIABLE
DECLARE @ColumnNames Table
(
      ID INT PRIMARY KEY IDENTITY(1,1),
      ColumnName VARCHAR(50)
)

--INSERT THE USERNAMES IN TO THE TABLE VARIABLE
--YOU CAN UNCOMMENT THE BELOW SELECT STATEMENTS TO SEE WHAT IS HAPPENING
INSERT INTO @ColumnNames
SELECT DISTINCT username FROM [dbo].[TimeLogging]

SELECT @MaxCount = @@ROWCOUNT--COLLECT MAXIMUM OF USER NAMES COUNT

--SELECT @MaxCount
--SELECT * FROM @ColumnNames
--CREATE A TEMP TABLE WHERE WE ARE GOING TO STORE THE DATA
--INITIALLY CREATE A TABLE WITH ONLY TWO COLUMNS

CREATE TABLE #Temp
(
      ID INT PRIMARY KEY IDENTITY(1,1),
      [Date] VARCHAR(50)
)

--alter TABLE #Temp add [bubai] decimal(18,2)
--NOW WE NEED TO ADD ALL THE USERNAMES AS COLUMNS IN THE #TEMP TABLE

DECLARE @SQL VARCHAR(max)
DECLARE @ColumnName VARCHAR(50)
--LOOP THRU THE USERNAMES

WHILE(@Count <= @MaxCount)
BEGIN
       SET @SQL = ''
      
       --GETTING USERNAMES ONE BY ONE
       SELECT @ColumnName = Columnname FROM @ColumnNames where ID = @Count
       --NOW CREATING A ALTER STATEMENT TO ADD A NEW COLUMN WITH USERNAME
       SET @SQL = 'alter TABLE #Temp add ['+@ColumnName+'] decimal(18,2) default 0'
       Exec(@SQL)--EXECUTE THE ALTER STATEMENT
       SET @Count = @Count+1
END

--SELECT * FROM #Temp--UNCOMMENT THIS TO CHECK THE COLUMNS NOW

SET @SQL = ''
DECLARE @ColumnNameslist VARCHAR(max)=''

SELECT @ColumnNameslist = '['+ColumnName+']' + ','+@ColumnNameslist  FROM @ColumnNames
SELECT @ColumnNameslist = LEFT(@ColumnNameslist, LEN(@ColumnNameslist) - 1)
--SELECT @ColumnNameslist --NOW WE ARE CREATING COLUMN NAMES LIST

DECLARE @AnotherColumnNamesList VARCHAR(max)=''
SELECT @AnotherColumnNamesList = 'ISNULL(['+ColumnName+'],0)' + ','+@AnotherColumnNamesList  FROM @ColumnNames

SELECT @AnotherColumnNamesList = LEFT(@AnotherColumnNamesList, LEN(@AnotherColumnNamesList) - 1)

--SELECT @AnotherColumnNamesList--ANOTHER COLUMN LIST

--THESE TWO COLUMN LISTS ARE USED TO BUILD THE PIVOT SQL STATEMENT
--BUILDING THE PIVOT TABLE AND INSERTING DATA INTO TEMP TABLES

SELECT @SQL = 'Insert INTO #Temp ( [Date],'+@ColumnNameslist+')
SELECT [Date],'+@AnotherColumnNamesList+'
FROM

(

SELECT [Date],[timelogged],[username]

FROM [dbo].[TimeLogging]

)
as S
Pivot
(

SUM([timelogged])
FOR username IN ('+@ColumnNameslist+')
)

as P'
--SELECT @SQL
EXEC(@SQL)
--SELECT * FROM #Temp
DECLARE @MaxColumnList VARCHAR(max)=''
SELECT @MaxColumnList = 'Max(['+ColumnName+']) as ' + ColumnName + ','+@MaxColumnList  FROM @ColumnNames
SELECT @MaxColumnList = LEFT(@MaxColumnList, LEN(@MaxColumnList) - 1)

--SELECT @MaxColumnList

DECLARE @MaxColumnListGroup VARCHAR(max)=''
SELECT @MaxColumnListGroup = 'Max(['+ColumnName+'])'+ ' + '+@MaxColumnListGroup  FROM @ColumnNames
SELECT @MaxColumnListGroup = LEFT(@MaxColumnListGroup, LEN(@MaxColumnListGroup) - 1)
--SELECT @MaxColumnListGroup

DECLARE @SumColumnListGroup VARCHAR(max)=''
SELECT @SumColumnListGroup = 'SUM(['+ColumnName+'])'+ ' , '+@SumColumnListGroup  FROM @ColumnNames
SELECT @SumColumnListGroup = LEFT(@SumColumnListGroup, LEN(@SumColumnListGroup) - 1)

--SELECT @SumColumnListGroup

SET @SQL = ''
SELECT @SQL = 'SELECT Convert(nvarchar(30),Cast([Date] as Date),101) as Date,'+@MaxColumnList+',('+@MaxColumnListGroup+') as Total

FROM #Temp
group by Date
union

SELECT '''+'Total'+''', '+@SumColumnListGroup+',SUM('+Replace(@ColumnNameslist,',','+')+') FROM  #Temp'
--SELECT @SQL

EXEC(@SQL)

drop TABLE #Temp

Run the query and the output is as in the following.

This query dynamically changes with the number of users inserted into the table. You should be able to display the result in the above format with as many users as are inserted into the table.