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.