Saturday, August 18, 2012

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

4 comments:

bubai banerjee said...

Its is really Good sir.But how shall i display it in Gridview(Front End UI)

Akki said...

Hi Bubai,

I have updated the post now to include the code to display. Let me know if you have any question.

bubai banerjee said...

Hi Sir,
Thanks for your kind help.but in my table there are lot of columns.(approx. 16).i only show 7 columns from them.my question is how to display these particular 7 records in Bound/Template fields as they are in pivot manner.(user name in pivot structure)Please help me Sir.Don't feel bad that i am disturbing you again and again.As i am very new in the system ,That's why.
Thanks and Regards
Bubai Banerjee

Akki said...

Oh I am sorry I did not check the comments section.


That was my question indeed. How do you decide which columns to display? If you have 100 users, do you want to display 100 columns? Please ask any questions on article comments so that I can respond to you immediately rather than sending an email which I will not check everyday.

Give me one set of data and tell me which columns you want to display and I will do my best to respond to you with the answer.

Post a Comment