Displaying rows as columns with total displayed on each row and column

Scenario:

I have a table with the following columns as below:

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 below:

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 unique date displayed in each row and show the total on each row and column. The output should be as below:

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                    7.80         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 going to user 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 below:

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                    7.80         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 are inserted into the table.

Displaying the above data in a grid view:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridBind._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    <asp:GridView runat="server" ID="PivotGrid">

        

    </asp:GridView>

    </div>

    </form>

</body>

</html>

Here is the code behind:

 public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            SqlConnection sqlConnection = new SqlConnection();

            sqlConnection.ConnectionString =

                "Data Source=(local);Initial Catalog=MyDB;Persist Security Info=True;User ID=userid;Password=password;Pooling=False";

            SqlCommand sqlCommand = new SqlCommand();

            sqlCommand.Connection = sqlConnection;

            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlCommand.CommandText = "DisplayPivotResults";

            sqlConnection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);

            DataSet ds = new DataSet();

            

            adapter.Fill(ds);

            PivotGrid.DataSource = ds;

            PivotGrid.DataBind();

        }

    }

Here is the stored procedure:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters 

-- command (Ctrl-Shift-M) to fill in the parameter 

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

CREATE PROCEDURE DisplayPivotResults

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @Count INT = 1

DECLARE @MaxCount INT = 0

DECLARE @ColumnNames Table

(

ID INT PRIMARY KEY IDENTITY(1,1),

ColumnName VARCHAR(50)

)

INSERT INTO @ColumnNames

SELECT DISTINCT username FROM [dbo].[TimeLogging]

SELECT @MaxCount = @@ROWCOUNT

--SELECT @MaxCount

--SELECT * FROM @ColumnNames

CREATE TABLE #Temp

(

ID INT PRIMARY KEY IDENTITY(1,1),

[Date] VARCHAR(50)

)

--alter TABLE #Temp add [bubai] decimal(18,2)

DECLARE @SQL VARCHAR(max)

DECLARE @ColumnName VARCHAR(50)

WHILE(@Count <= @MaxCount)

BEGIN

SET @SQL = ''

SELECT @ColumnName = Columnname FROM @ColumnNames where ID = @Count

SET @SQL = 'alter TABLE #Temp add ['+@ColumnName+'] decimal(18,2) default 0'

Exec(@SQL)

SET @Count = @Count+1

END

--SELECT * FROM #Temp

SET @SQL = ''

DECLARE @ColumnNameslist VARCHAR(max)=''

SELECT @ColumnNameslist = '['+ColumnName+']' + ','+@ColumnNameslist  FROM @ColumnNames

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

--SELECT @ColumnNameslist

DECLARE @AnotherColumnNamesList VARCHAR(max)=''

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

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

--SELECT @AnotherColumnNamesList

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

   

END

GO