Grouping in data table ( removing duplicates )

[make me rain]

please excuse this thread is for / in connection with this thread

in a datatble, say for example table 3 columns
emp_id
emp_name
emp_class

here i don't want field emp_id to repeat for each row,
i mean i want to set empty value in the data table keeping only the first row entry
this check & update needs to be done for each emp_id.

i have done it by comparing each row with all the row and then setting the values to empty
very nasty & resource consuming & time consuming & harder to code

please some professional approach for this please.

[wes4dbt]

If you setup the table with emp_id as the Primary Key, the database wont let you enter duplicate emp_id.

[make me rain]

no no,
i am failed to visualize you, sorry

lets say 2 tables

table T1
emp_id (Pk)
emp_name
emp_class

table T2
emp_id (Fk)
Consumption
Consumption_date

now

SELECT t1.emp_name,t2.consumption_date,t2.Consumption from t1 left join t2 on t1._emp_id = t2.emp_id group by t1.emp_id;

in the query above t1.emp_name will repeat redundantly, this i want to eliminate so as to retain only one emp_name for all his relative records
and then next emp_name & so on

this query will be loaded in to the dataset, further i need help please

[Niya]

What the ?....That shouldn't even be legal. SQL Server would never allow that as you need to aggregate non grouped fields. In such a case the name would not be repeated. I had no idea MySql allows that abomination....but then again, what do I know. I'm curious to see what the guys more experienced with different databases would have to say about this.

[make me rain]

this is the query out put
Attachment 97485
i need to eliminate duplicate names in datatable or in the query & the out must be
Attachment 97487

[dunfiddlin]

Can't be done, shouldn't be done. Unless you're prepared to transfer to a listview and use that column as the group property on each item.

[kareninstructor]

At the DataTable level, I would look at DataTable.Merge method to eliminate dups.
Or perhaps use the following DataGridView to group by column

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

[make me rain]

kevin after long time i am getting you
i need to do it in datatable only,
a kind of looping + comparing prev row with next row + editing the datatble columns, it works but time consuming

is the LINQ can simplify this task ?

[kareninstructor]

I would had thought the DataGridView I suggested would had done the job
Attachment 97527