Row grouping in DataGridView using winforms

Here is a custom DataGridView that you add to your project, compile, add the DataGridView to a form, set the DataSource of the DataGridView to say a DataTable with repeating data, build, run, should look like the image below as a sample.

Source

''' <summary>
''' Original author
''' http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/a44622c0-74e1-463b-97b9-27b87513747e#faq8
''' </summary>
''' <remarks>
''' Original code was in C Sharp, I converted and tweaked some code
''' which did not compile under VB.NET
''' </remarks>
Public Class GroupByGrid
    Inherits DataGridView
    Protected Overrides Sub OnCellFormatting(ByVal args As DataGridViewCellFormattingEventArgs)
        MyBase.OnCellFormatting(args)
        ' First row always displays
        If args.RowIndex = 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.Value = String.Empty
            args.FormattingApplied = True
        End If
    End Sub
    Private Function IsRepeatedCellValue(ByVal rowIndex As Integer, ByVal colIndex As Integer) As Boolean
        Dim currCell As DataGridViewCell = Rows(rowIndex).Cells(colIndex)
        Dim prevCell As DataGridViewCell = Rows(rowIndex - 1).Cells(colIndex)
        If (currCell.Value Is prevCell.Value) OrElse (currCell.Value IsNot Nothing AndAlso prevCell.Value IsNot Nothing AndAlso currCell.Value.ToString() = prevCell.Value.ToString()) Then
            Return True
        Else
            Return False
        End If
    End Function
    Protected Overrides Sub OnCellPainting(ByVal args As DataGridViewCellPaintingEventArgs)
        MyBase.OnCellPainting(args)
        args.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
        ' Ignore column and row headers and first row
        If args.RowIndex < 1 OrElse args.ColumnIndex < 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
        Else
            args.AdvancedBorderStyle.Top = AdvancedCellBorderStyle.Top
        End If
    End Sub
End Class

Serial Number to be given with grouping in Report Viewer

Serial Number to be given with grouping in Report Viewer

We could achieve this requirement in RDLC report, please follow these detailed steps to make it:

1. Right-click the whole matrix  control, and then select Properties.
2. Switch to Groups tab, click Add button to add another Row group, in the expression textbox, please select groupedcolumn1 datafield(This is very important)
3.  Then click the new row group, then click Up button to move it to the top1 location.
4. Click OK
5. Switch to the report’s design surface, then type in the expression =Runningvalue(Fields!GroupColumn1Name.Value,countdistinct,"MatrixName") in the textbox created in step3.

Note: In the expression, Please make sure the datafield name in the group1(Fields!GroupColumn1Name) and Matrix name(MatrixName) is correct.

Preview the report, you will get another column in left to display 1, 2, 3, 4, ……

ex:  =Runningvalue(Fields!InvoiceNo.Value,countdistinct,"Tablix1")

OR

=RunningValue(Fields!GroupName.Value, countdistinct,Nothing)

SQL Server Split Function Example in 2008 to Split Comma Separated String into Table

Introduction: 

Here I will explain simple split function in SQL Server to split comma separated string into table values inSQL Server database or How to split comma separated string with custom split() function in SQL Server. 

Description:

In previous articles I explained Pass table as parameter to stored procedure in SQL Server, Different types of joins in SQL Server, Difference between len & datalength functions in SQL Server, convert rows to columns in sql server without using pivot, Create database schema diagrams in SQL Server, Delete Duplicate records from SQL Server and many articles relating to SQL Server. Now I will explain how to create split function in SQL Server.

To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below

Custom Split function to split comma separated string into table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z --     ERO FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Items) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END

Once we create custom function Split() run sample query like as shown below

SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',')

Once we run above query we will get output like as shown below

Output