Pivot Result Display

Bubai Banerjee

14y6.7k.NET

Hi Sir,

I have doubt can you please help me..I am very new in s/w Development.

below i am describing.

Original Table Structure.

ID Date TimeLogged UserName

1 10/8/2012 5.50 Bubai

2 11/8/2012 2.30 Bubai

3 10/8/2012 3.30 Bhanu

4 11/8/2012 7.30 Bhanu

I want result like below. User Name should be dynamic. May be lot of

users. User name will come from Database table. Please give me broad

description as I am very new in the system, for my understanding.

Date Bubai Bhanu Total

10/8/2012 5.30 3 8. 30

11/8/2012 2.30 7.30 10

Total 8 10.30 18.30

Please help me to solve this issue… It's really urgent

if you can reply early I'll really appreciate. Thanks in advance.

Thanks and Regards
Bubai Banerjee

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bubai Banerjee14y ago
Hi Sir,
Lot of Thanks for your kind help.
i know that how to bind the Gridview fom datasource.But my question is how shall i bind the Gridview with selected columns.As i have a lot of columns in gridview( i only show the selected columns to the users)as per my requirment.
as store procedure shows the pivot result.so my question is that how i bind username columns with bound or Template columns in Gridview(Asp.net).
so i am requesting you please help me for the same.
Thanks and Regards
Bubai Banerjee
Akkiraju Ivaturi14y ago
Hi Bubai,

Basically, you need to follow these 3 steps:

1. Create a stored procedure.
2. Create a web application or web site.
3. Create a Gridview control in aspx file and add the code in the code behind.

Let me know if you need any help. I would recommend considering pagination instead of displaying the data as it comes from stored procedure.

If you are satisfied with the answer, do not forget to mark it as accepted :)
Akkiraju Ivaturi14y ago
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:
-- Create date:
-- 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
Akkiraju Ivaturi14y ago
Hi Bubai,

Happy to know that it was useful for you.

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

Untitled Page

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();

}
}
Nattudurai Eswaramurthy14y ago
Hi,
Write a procedure to return the value from SP(Which returns pivotal value)

bind the data to gridview. either u can create a model and bind it or simply bind the datasource to gridview
Bubai Banerjee14y ago
Hi Sir,
A heartly thanks to you as you save me from danger.Thanks a lot once again.But Sir,Please help me a little more to "How i display that result in front end Gridviiew (Asp.net).Because that is my requirement to display result in front end.please attach some more code with this blog please.

Thanks and Regards
Bubai Banerjee
Akkiraju Ivaturi14y ago
If you need any help on this let me know... We can try it out....
Akkiraju Ivaturi14y ago
Hi Bubai,

I have taken your problem and created an article. Just look at the following link and it may help you.

http://ilovemicrosoft.blogspot.com/2012/08/displaying-rows-as-columns-with-total.html