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

Scenario

I have a table with the following columns as in the following:

CREATE TABLE [dbo].[TimeLogging](

      [ID] [int] NOT NULL,

      [Date] [date] NULL,

      [timelogged] [decimal](18, 2) NULL,

      [username] [varchar](50) NULL,

 CONSTRAINT [PK_TimeLogging] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

I have data something as in the following:

ID Date timelogged username
1 2012-10-08 5.50 Ravi
2 2012-11-08 2.30 Ravi
3 2012-10-08 3.30 Joe
4 2012-11-08 7.30 Joe
5 2012-11-09 8.30 Marie
6 2012-12-05 99.90 John
7 2009-06-09 78.78 Kelly

Problem Statement

I want this data to be displayed with all the user names as columns in the results with a unique date displayed in each row and show the total on each row and column. The output should be as in the following:

Date Ravi Marie Kelly John Joe Total
06/09/2009 0.00 0.00 78.78 0.00 0.00 78.78
10/08/2012 5.50 0.00 0.00 0.00 3.30 8.80
11/08/2012 2.30 0.00 0.00 0.00 7.30 9.60
11/09/2012 0.00 8.30 0.00 0.00 0.00 8.30
12/05/2012 0.00 0.00 0.00 99.90 0.00 99.90
Total 78.78 8.30 78.78 99.90 10.60 205.38

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:

Date Ravi Marie Kelly John Joe Total
06/09/2009 0.00 0.00 78.78 0.00 0.00 78.78
10/08/2012 5.50 0.00 0.00 0.00 3.30 8.80
11/08/2012 2.30 0.00 0.00 0.00 7.30 9.60
11/09/2012 0.00 8.30 0.00 0.00 0.00 8.30
12/05/2012 0.00 0.00 0.00 99.90 0.00 99.90
Total 78.78 8.30 78.78 99.90 10.60 205.38

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.
 

Next Recommended Readings