Pivot Result Display

Question

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

bubai ban · Answer

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 Ivaturi · Answer

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 Ivaturi · Answer

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(11) 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(11) [Date] VARCHAR(50) ) --alter TABLE #Temp add [bubai] decimal(182) DECLARE @SQL VARCHAR(max) DECLARE @ColumnName VARCHAR(50) WHILE(@Count BEGIN SET @SQL = '' SELECT @ColumnName = Columnname FROM @ColumnNames where ID = @Count SET @SQL = 'alter TABLE #Temp add ['+@ColumnName+'] decimal(182) 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 Ivaturi · Answer

Hi Bubai Happy to know that it was useful for you. Here is the aspx code: 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 Eswaramurthy · Answer

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 ban · Answer

Hi Sir A heartly thanks to you as you save me from danger.Thanks a lot once again.But SirPlease 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 Ivaturi · Answer

If you need any help on this let me know... We can try it out....

Akkiraju Ivaturi · Answer

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